In this unit students investigate the times taken and the distance travelled in coming to school, using a spreadsheet to produce data displays and investigate distributions. This unit follows on from Travel to School I, which contains a basic introduction to using Excel.
- plan an investigation;
- use spreadsheets to display and analyse data
- discuss features of data display
- compare features of data distributions
In this unit students use spreadsheets to produce data displays and investigate distributions
Copymaster of Sample Table
Copymaster of Tallysheet (time)
Computer with Excel spreadsheet (or equivalent) and Internet Access
Spreadsheet Tutorial (see Related Resources if needed)
census, distributions, spreadsheets, estimate, frequency table, sample table, summary table, unequal classes, percentages, averages, mode, media, mean, raw data, sum of values
Prior to this unit.
The students will need to collect data on how long it takes them to get to school andthe distance travelled.
Session 1: Looking at our travel data
- Discuss the kind of data that the class has collected about their daily travel to school. They have the time taken – some will be measured in seconds, minutes and even hours. They have the distance traveled – this will be in meters and kilometers and we can reinforce the use of decimal notation e.g. 3.4 km.
- Identify ways of organizing and presenting this data in an easy to read form. A discussion of the difference between this kind of data which measures a continuously varying quantity and the category data in Travel to School Unit I is important.
- Students spend the rest of the session in groups organising the data in ways they decide on. They present and discuss the results of their work to the class.
Session 2: A standard way of organising our travel data
- In this session we introduce the idea of a frequency table.
- Discuss the idea of grouping the times and distances in order to represent this data. Introduce the idea and value of a frequency table and after allowing students the opportunity to make their own frequency tables you can use the tally sheets provided as copymasters.
Note: these sheets have been laid out in a form that will enable easier comparison with New Zealand (time taken) and UK (distance travelled) data in the next sessions. There should be some discussion about the unequal classes in the distance frequency table, and whether this is, in general, a good idea. You might want to produce two tables, one with equal classes.
- We are going to use the computer to help us display the results again. Students enter the data into Excel and then spend time deciding which graph(s) best display the data. They will no doubt try pie, line and bar graphs.
- A discussion of the graph results and why some ways of representing the data are more appropriate than others.
Session 3: How do we compare – distance travelled?
- We will again make a comparison with the data from other students from around the world who have also been asked how long it takes them to travel to school and how far they travel. This data has been collected and is available at the CensusAtSchool international site.
- Get the students to find the Travel to School data for the students in the UK by using the DataTool (requires Flash). After you have made each selection, click the next button at the top right of the tool.
- Choose UK, and a year to compare with.
- Choose age and travel to school as the variables.
- Choose to only use part of the database.
- Double click on Age, and choose an appropriate age to compare with your class (you can choose a range of just one age). You need to click save this option before continuing.
- Drag the two variables onto the axes of the Tabulated data box (in the middle) and click the small play button. This will give you a sample randomly taken from the database of results.
- Once the data has been collected from the site get the students to discuss how they might make a comparison with their own data. Consider questions such as:
Does it matter that the table lists percentages for each distance class travel?
Which of the columns of data should we take?
What should we do with the ‘not stated’ class?
- Once the students have decided on their approach they can be encouraged to type the data into Excel and use Excel to draw the graph(s) they need (save the tables for a later session). They will need to consider the format they will enter the data in, whether they will need headings, etc. While the pie graph is again the easiest to make the comparison with they may come up with other methods. One way is to convert the class numbers into percentages using Excel and then to draw bar graphs.
- Conclude with a reporting time where the students are given the chance to show their graphs and to discuss what they have found out. Can they provide possible explanations for differences?
Session 4: How do we compare – time taken?
- This is a repeat of Session 3 but using the data for New Zealand on the CensusAtSchool site.
- Choose "Explore the data". Under the "Databases" heading, choose a year, and click on the "Table Maker" link. Choose "age" or "year" as variable 1 and "timetravel" as variable 2. You can collate the times (for example combine 1-5, 6-10 etc) by dragging one column headings onto another.
- Once again they use Excel to produce the graphs they need to make the comparisons they want (save the tables for a later session).
- Again conclude with a reporting time where the students are given the chance to show their graphs and to discuss what they have found out. Stress the importance of trying to give reasons for any differences they have found.
Session 5: Comparisons using ‘averages’.
Note that measures of centre are not introduced in The New Zealand Curriculum until level 5. This material may not be suitable for all of your students.
- Discuss the value of having a single figure to compare two sets of data and get ideas on what might be a good one to use.
- Introduce the idea of the mode (with the greatest frequency), and the median (the middle value). Discuss how we find the middle value for a distribution. Compare the time taken (class and NZ) and distance travelled (class and UK) using these two statistics.
- The idea of the mean is more difficult. Introduce it as the sum of the values divided by the total number of values. Get them to calculate the mean time and distance for their data, using the original raw data. This could be done by hand or by entering the raw data into Excel and using the average formula (for example entering in A36 the formula = average(A1:A35)).
- However to make the comparison we have to have a way of estimating this exact average called the mean from a frequency table. Introduce the idea of representing the class by its middle value. We find the middle value by adding up the end points and dividing by 2 (Note - this is easier for the distance than the timetable. For distance we get 0. 5, 1. 5, 2. 5 etc, but for time it is necessary to talk about what goes into the 10-19 class etc. Does 19 minutes 22 secs? Does 19 minutes 48 secs? This leads to 14.5 minutes as the mid point. The first class is also a problem since there is no –0.5 minutes. 4.75 minutes is the mid point here. )
- Students go to their tables in Excel (previously saved). First they use there data to check the mean as an estimate of the actual mean they have calculated. They can either insert a column of mid-points between the two columns (better) or put it on the end as a third column. Get Excel to calculate the mid-point multiplied by the frequency and put it in a fourth column. Total this column and the frequency column and divide to find the mean. This might look like the Sample Table (see copymaster). Compare the answer with the actual mean and discuss estimates and how good they are.
- Students compare the UK and NZ means with their class means for distance and time respectively and try to come to conclusions. Does it confirm or alter their previous ideas?