FOR ALL STUDENTS:

In this lab you will take another look at the variation in a set of measurements in an automotive setting. Each team of Davea students will measure the oil pressure (at idle, 1000 RPM, 2000 RPM, and 2500 RPM) and enter the data on a spreadsheet software like Excel 5.0. In order to analyze the variation in the measurements made, it is essential that the oil pressure of the same car be measured by each team. Otherwise, we would be looking at the variation in oil pressure among a set of different cars.

Day 3 

Day 3 will be devoted primarily to making the necessary measurements and recording the individual and team data in a spreadsheet. The spreadsheet can be set up in a fashion similar to sample spreadsheet provided.

Oil Pressure at Idle (an Excel 7.0 file)

(This spreadsheet has been designed under the assumption that all the students will be measuring the same quantity on the same car but, with only a little modification it could be used to analyze data from many cars.) The students should work in teams of about five, but each student will make and record his or her own measurement. In the sample, these are located in column C. Notice that the students are grouped according to teams. In column D the students of each team will calculate their team average. This can be done by making use of Excel's AVERAGE function. In the sample Team 1 entered the following formula in cell D4:

=AVERAGE(C2:C6)

This is asking Excel to enter the average, or mean, of the numbers in cell C2 down through cell C6, which contain the individual measurements for Team 1. Do not forget to start with an equal sign when entering a formula or Excel will just enter the text "AVERAGE(C2:C6)" in cell D4.

Day 4

Day 4 will be spent learning to use Excel to analyze the data and drawing conclusions from the analysis. This portion of the lab will run more efficiently if each team has access to a computer. Each team should open a copy of the spreadsheet containing the previous day's data. Off to the side of the spreadsheet in cell I4 (see the sample), use the average function to find the mean for the entire class. In the cell next to it enter "class mean" (without an equal sign) so that you will know to what quantity the number in the adjacent cell refers.

Note: Not only are the built-in spreadsheet functions like AVERAGE faster to use than a calculator, but with them the spreadsheet is very versatile. For example, if you made a mistake in recording any of you original data, a new average will be computed automatically as soon as the correction is made!

Column E will contain the individual deviations from the class mean that you just computed. These deviations tell how far away each measurement was from the class average. For example, Connie from Team 4 measured the pressure to be 6.328 psi, which was 0.001 psi below the mean for the whole class. Therefore, her measurement deviated from the mean by 0.001 psi. Here we are just interested in how far away the measurements are from the mean and not whether they were above or below it. For this reason we will use Excel's absolute value function. In cell E2 enter the following formula:

=ABS(C2-I$4)

This will find the deviation of Joe's measurement from the class mean since Joe's individual measurement is located in cell C2 and the class mean was computed in cell I4. The dollar sign in the formula will be explained shortly. Notice that functions in Excel operate on whatever is inside the parentheses. In this case, we're taking the absolute value of the difference of the values found in two different cells.

So far, we have just found the deviation for the first person in Team 1. We could enter a similar formula for each person, but that would be time consuming. Excel can copy that formula into all the cells in column E, automatically adjusting them so that in the next row the formula would read =ABS(C3-I$4), and in the row after that, =ABS(C4-I$4), and so on. This will allow us to find the deviations for each student without typing in a separate formula for each. The dollar sign between the "I" and the "4" will keep that cell the same in each formula, which is what we want since that is the only cell that contains the class mean. To "fill down" like this, select cell E2 (click on it once) and then position the pointer to the lower right hand corner of the cell. Here the pointer should change from a fat, white plus sign to a thin, black plus sign. Now click the left mouse button, hold it, and drag it down column E to the bottom of the data table. Then release the button. The new individual deviations should then appear as if by magic. If you select any of the cells that you have just filled, you should see the appropriate formula in the formula bar above. Try it.

In column F you will need to find more deviations, but this time the deviation of each team from the class average. Select cell F4 and see if you can enter the correct formula to do this for Team 1. If you are new to spreadsheets, you may want to simply repeat this procedure in the appropriate cell for each team. A quicker option would be, rather than filling down as you did last time, to copy cell F4 (select it and choose Copy from the Edit menu). Then select the other cells that will contain team deviations (cells F9, F14, and F19) simultaneously by holding down the Control button while you're selecting these cells the mouse. Then from the Edit menu choose paste.

Now let's find the range of the class data. The range is how far the data spans, that is, the difference between the largest and smallest pieces of data. Here you have at least three options, with option 3 being the most efficient if you're familiar with spreadsheets:

• Option 1: Visually scan the class data for the largest and smallest measurements. In the example, Melissa had the largest measurement, while Tom and Tony tied for the smallest. So, in cell I2 you could enter the formula =C3-C9 to compute the range. This method is not much fun, however, and likely to result in errors, especially when data lists are long.
• Option 2: Select the list of measurements in column C. Copy and paste them somewhere else in spreadsheet. Then, with the new list still selected, choose Sort from the Data menu. This will list them in ascending order. The range of the data, therefore, is the bottom entry minus the top.
• Option 3: In cell I2 simply enter the following formula 
  • =MAX(C2:C21)-MIN(C2:C21)

The MAX function finds the largest value in the data contained in cells C2 through C21. The MIN function finds the smallest value among the same cells. Then the two values are subtracted, yielding the range of the data.

In cell I3, see if you and your team can use the MEDIAN function to find the median of the individual measurements. In the example, the median corresponds to the oil pressure that would be found right in the middle of a list of toil pressures arranged in ascending order. That is, there will be the same number of measurements above the median as below. If there are an even number of measurements, as there are in the example, the median is the average of the two middle values in the sorted list. When sorted, as described in option 2 above, the tenth and eleventh measurements in the example data are 6.329 psi. (Jodie and Sarah) and 6.330 psi. (Ben and Amy). Use this sorting method to see if you get the same median as you did with the built-in Excel function.

Now consult your sorted list to see to determine which value came up the most. This value is called the mode. In the example, 6.331 psi. came up three times, along with 6.328. These are both the mode. The Excel program prints only one of them. See if Excel returns the same value when you use the MODE function on the measurements in column C.

To finish off your spreadsheet, use Excel to compute the mean team average (the average of the team averages), the mean individual deviation, and the mean team deviation.

DATA ANALYSIS FOR ALL STUDENTS:

1. Compare the average of the team averages to the class average. Explain how you could predict how close these values are to each other in terms of how varied the sizes of the teams are. How should they compare if all the teams have exactly the same number of people.
2. Is the mode the largest measurement made? Does it have to be? Can it be? Explain.
3. Which is more, the mean individual deviation or the mean team deviation? Is this what you would expect? Explain.
4. Which do you think is likely to have more accuracy, an individual measurement a team average?
5. Does the variation within a team appear to "smooth out" any individual measurements that seem very high or very low? Why does this happen?
6. If accuracy were very important, would you prefer to be part of a large team or a small one. Explain.

Optional lab for high school students in DuPage county:

In this lab you will take another look at the variation in a set of measurements in an automotive setting. Each team of students will measure the thickness of a brake pad (or make some other accessible measurement such as oil pressure, spark plug gaps, etc., as determined by your instructor). In order to analyze the variation in the measurements made, it is essential that the same brake pad be measured by each team. Otherwise, we would be looking at the variation in thicknesses among a set of different brake pads. The teams should record their measurements using spreadsheet software like Excel 5.0.

Brake Disc Thickness (An Excel 5.0 file)

-Back to Day 1 Lesson-   -Go to Day 5 Lesson-