In this unit students investigate variables associated with cars, using a spreadsheet to produce data displays and investigate distributions.
- plan an investigation
- use spreadsheets to display and analyse data
- discuss features of data display
- compare features of data distributions
This unit involves students collecting statistics and analysing them in a variety of ways using Excel.
Spreadsheet tutorial (see Related Resources if needed)
Copymaster Car scattergraph
Car data file 1 (attached file)
Car data file 2 with km/l (attached file)
spreadsheet, data, data distribution, variables, mode, median, mean, averages, frequency, contiguous, scattergraph, scatter plot
Session 1: Car performance indicators
- Discuss what kind of data the class could collect to find out about the performance of cars.
- Identify variables including number of cylinders, Horse Power (HP), weight, km per litre of petrol, acceleration 0-100 km, etc.
- Posing questions: students are encouraged to think of questions they would like to know the answer to about car performance and its relationship to the variables identified or to be identified. For example:
Does having more cylinders mean increased HP?
Is larger HP associated with worse km per litre performance?, etc
(Note not all the questions can be answered at this level!)
- Students help to select data to collect as a class. Each student is encouraged to collect data from four cars for the variables identified (these should include number of cylinders, HP, weight and km per litre for comparison later in the unit). This can be from home manuals or from garages – caution should be exercised in students’ collection of data. Encourage assistance from families. Student data will be returned to in a later session.
Session 2: A car data file
- There is a data file called Car Data File 1 (attached). It has information which can be related to car performance.
- Discuss what are the best ways to represent this data so we can answer some of our questions. Explain that we are going to use the computer to help us.
- The first problem is that since this is USA data the petrol performance is given in miles per gallon not in km per litre, so we need to get Excel to change this for us. Discuss the mathematics of what we need to do. We can use the facts that 1 gallon is about 4. 5 litres and 1 mile is about 1. 6 km. So we need to take the table and insert a column after column B and label it km/l. Then take the mpg (miles per gallon) column and use Excel to work out each value multiplied by 1. 6 and divided by 4. 5 (do this by entering the formula =B2*1. 6/4. 5 into C2 and then fill down as far as C33). Round the values to 1 dp using the format cells option. The attached file called Car Data File 2 is what the file looks like now with km/l
- Allow the students time to investigate what sort of graphs they can draw from the data (remember that to use two columns that are not next to each other (not contiguous) we have to hold down the Ctrl key when selecting the second column).
- They discuss their graphs and problems with the rest of the class.
Session 3: 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.
- What statistics can we find from this table?
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. Calculate these two statistics for the HP, number of cylinders and number of carburettors. Talk about why the median is OK for the km/l but the mode is less useful.
- 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 HP for the data using the Excel spreadsheet and the average formula (for example entering in E34 the formula =average(E2:E33)).
- The students can explore other averages on the spreadsheet. They then can discuss what these values tell us.
Session 4: Looking at our data
- The students enter their own car data into a spreadsheet, setting it out in a similar way to the Excel file used above.
- Students analyse their data in the same way as the car data provided. Are there any differences noted between the USA cars and the cars in New Zealand. Can they provide reasons for any differences they note?
Session 5: What has been missing?
- Although we have some interesting data and statistics we have not been able to answer some of the most interesting questions. Why not? We have not been able to compare two variables directly, only by looking at their averages.
- Although the idea of a scattergraph is a more advanced one in statistics it is useful to introduce it here. Excel can easily draw these for use and we can talk about what they tell use.
- Use Excel to draw a scattergraph (use the XY (scatter) graph option) for km/l against HP. The copymaster called Car Scattergraph is what it might look. Although we will not model this the students can discuss what they think it tells us. As we increase the HP does the km/l get lower?, etc.
- The students draw other scattergraphs for the given data and their own and then come together to discuss their findings. Can they give reasons for what they think they have found?