Question

The data below are from a study conducted by a consumer research group on the fuel efficiency (rated based on city miles per gallon) of the 30 top-selling U.S. automobiles.

23 20 16 13 34 27

24 10 16 12 34 26

14 31 15 12 16 36

18 22 15 19 28 38

10 16 14 23 19 44

1. Enter the data into a spreadsheet. Be sure to clearly label and neatly format your spreadsheet.

2. Calculate the sum of these data two different ways: a. By writing your own formula. Clearly label the result. b. By using the SUM spreadsheet function. Clearly label the result

. 3. Use the COUNT function to calculate the N of this sample data set. Clearly label the result.

4. Calculate the arithmetic average (mean) of these data by writing your own formula. Clearly label.

5. Create a new column of data in which you subtract the mean fuel efficiency from each individual fuel efficiency score (i.e., MPG – mean). Be sure to use the proper relative and absolute references (indicated with $ signs) to perform these calculations.

6. Now compute the sum, N, and mean of your new (MPG – mean) scores.

7. Create one more new column of data in which you square each of the (MPG – mean) scores. 8. Finally, compute the sum, N, and mean of your new (MPG – mean)^2 scores

Answer 1

1. Check the excel sheet at the end (can only paste image)

2. a.)  =E2+E3+E4+E5+E6+E7+E8+E9+E10+E11+E12+E13+E14+E15+E16+E17+E18+E19+E20+E21+E22+E23+E24+E25+E26+E27+E28+E29+E30+E31

b.) =SUM(E2:E31)

3. =COUNT(E2:E31)

4. =(E2+E3+E4+E5+E6+E7+E8+E9+E10+E11+E12+E13+E14+E15+E16+E17+E18+E19+E20+E21+E22+E23+E24+E25+E26+E27+E28+E29+E30+E31)/30 (instead of 30 you can use the reference of count function)

5. =$F$5-E2 (use this formula to populate the new column)

6. Sum =SUM(J2:J31) | N =COUNT(J2:J31) | Mean =AVERAGE(J2:J31)

7. use the formula =J2^2 to populate the entire column

8. Sum =SUM(N2:N31) | N =COUNT(N2:N31) | Mean =AVERAGE(N2:N31)

Appendix 1

Appendix 2

Appendix 3