Data concept:
Band:
Suggested year level:
Relevant content description:
|
Collecting, managing and analysing data
5-6
Year 6
Acquire, store and validate different types of data, and use a range of software to interpret and visualise data to create information (ACTDIP016)
|
The concept for this teaching idea came from a professional development session conducted by Kylie Docherty in 2016 and was the result of a collaboration of a number of inspired and talented teachers.
This activity uses a number of more complex spreadsheet functions including data validation, V-LOOKUP, COUNTIF and EXACT. A teacher with experience and confidence in using these functions, who is teaching students who have some familiarity with basic spreadsheets might choose to develop the BFF from a blank spreadsheet as a collaborative effort. This was how the original BFF was developed in the professional development session. Teachers with less spreadsheeting experience might choose to use the Google Sheet template provided to explore the functionality of the tool.
Why do we need to validate data?
Usually when we are collecting data, it is because we plan to do something with that data. This means that we not only need to know that the data is accurate, but we also need to make sure it is in the right form.
In spreadsheets, there are different data types, and this effects what you can do with the data. If a "number" is written as a word, you won't be able to use it to make a chart. If there are misspellings or differences in wording or punctuation, the system might not recognise two items as a match.
A good way to illustrate this is by using an AnswerGarden to collect some data collaboratively and observe what happens when similar responses are not identical. While a human can determine that Pink, crimson, magenta and blush are all similar responses and group these together, the computer is unable to do these from a direct comparison.
We can use a simple activity to show why we might need to create categories for answers or provide options in a drop down when collecting data that we want to organise and present with a spreadsheet.
In spreadsheets, there are different data types, and this effects what you can do with the data. If a "number" is written as a word, you won't be able to use it to make a chart. If there are misspellings or differences in wording or punctuation, the system might not recognise two items as a match.
A good way to illustrate this is by using an AnswerGarden to collect some data collaboratively and observe what happens when similar responses are not identical. While a human can determine that Pink, crimson, magenta and blush are all similar responses and group these together, the computer is unable to do these from a direct comparison.
We can use a simple activity to show why we might need to create categories for answers or provide options in a drop down when collecting data that we want to organise and present with a spreadsheet.
Using the Best Friend Finder
The Best Friend Finder makes use of the VLOOKUP (vertical lookup) function to automatically populate a number of cells when a particular unique identifier is entered in one of the white cells. The VLOOKUP function specifies which column within the data table the data will be found in, and then it matches the row by finding the unique identifier (in this case, Name) from the first column. When two unique identifiers are entered, the data for each table row is listed in the lighter green cells. The data in each of the light green cells is compared using an EXACT function. If the two values are an exact match, the function returns TRUE, else, it returns FALSE. The number of TRUE matches are counted using a COUNTIF function, and this number is displayed as the "score".
Acquiring data
The fastest way to populate all of the cells in the table is to allow students to collaborate on the document. When collaborating digitally, it is important to establish some protocols with the class or else the activity becomes confusing when one student enters data in the same cell as another. Numbering off around the class (starting with 2, as the first row contains headers) is a useful method for allowing quick data collection.
Once the student data has been entered, students should make their own copy of the spreadsheet before experimenting so that they are no longer working in the collaborative space.
Once the student data has been entered, students should make their own copy of the spreadsheet before experimenting so that they are no longer working in the collaborative space.
Validating data
Since the VLOOKUP requires a unique identifier, it is important that the students understand this. This means that if there are two students with the same name within the class, a solution will need to be found so that each person has a unique name within the spreadsheet. This is a useful teaching point and helps students to reflect on how the VLOOKUP function works. Curious students might test what happens if two students enter the same name, but make different selections.
Because the EXACT function requires an exact match, the students are made to select responses from a drop down menu instead of typing them in themselves. Students could consider what would happen if they tried a similar activity but allowed the users to type the responses. How would capitalisation and spelling affect the functionality of the tool?
The drop down lists are created using "data validation". To add a drop down list in a spreadsheet, right-click (or control-click) on the cell in which you want the list to appear and select "data validation" from the menu. You will need to create the list of options for the drop down menu somewhere else within the document first. In the BFF, the lists are on the second sheet.
Adding drop-down menus is reasonably easy (once you know how) so even if you are using the template, the class could decide to change one of the questions and responses to explore how this works.
Because the EXACT function requires an exact match, the students are made to select responses from a drop down menu instead of typing them in themselves. Students could consider what would happen if they tried a similar activity but allowed the users to type the responses. How would capitalisation and spelling affect the functionality of the tool?
The drop down lists are created using "data validation". To add a drop down list in a spreadsheet, right-click (or control-click) on the cell in which you want the list to appear and select "data validation" from the menu. You will need to create the list of options for the drop down menu somewhere else within the document first. In the BFF, the lists are on the second sheet.
Adding drop-down menus is reasonably easy (once you know how) so even if you are using the template, the class could decide to change one of the questions and responses to explore how this works.
Using data to create a digital solution
The Best Friend Finder is a simple attempt to create a digital solution which interprets the data collected in the spreadsheet for the purpose of "finding the perfect match" for a student within the class.
Once students have developed an appreciation for how this spreadsheet tool works, they could develop their own digital solution using a different data set.
Maybe they could compare the data of two countries, or of two different animal species. A lot of students like to exchange trading cards, but there are sometimes disagreements over what makes a fair trade. Perhaps students could create a tool that compares two trading cards, allocates scores to particular features and then makes a total for each card that can be compared to determine if a swap is fair or not.
In a Science investigation into earthquakes, students could develop a tool that shows a comparison of the data between two different earthquakes. There is no need to calculate a "match" score if this does not make sense in the chosen situation.
Once students have developed an appreciation for how this spreadsheet tool works, they could develop their own digital solution using a different data set.
Maybe they could compare the data of two countries, or of two different animal species. A lot of students like to exchange trading cards, but there are sometimes disagreements over what makes a fair trade. Perhaps students could create a tool that compares two trading cards, allocates scores to particular features and then makes a total for each card that can be compared to determine if a swap is fair or not.
In a Science investigation into earthquakes, students could develop a tool that shows a comparison of the data between two different earthquakes. There is no need to calculate a "match" score if this does not make sense in the chosen situation.