Let’s create a spreadsheet to compute the mean and standard deviation of a column of numbers in two different ways. The spreadsheet here is a template for this exercise.

(a) Reproduce the template on your spreadsheet. Cells B4 to B8 contain the data (x values) whose mean and standard deviation we will compute. 

(b) Write a formula in cell B9 to compute the sum of numbers in B4 to B8.

(c) Write a formula in cell B10 to compute the mean value.

(d) Write a formula in cell C4 to compute (x - mean), where x is in cell B4 and the mean is in cell B10. Use Fill Down to compute values in cells C5 to C8.

(e) Write a formula in cell D4 to compute the square of the value in cell C4. Use Fill Down to compute values in cells D5 to D8.

(f) Write a formula in cell D9 to compute the sum of the numbers in cells D4 to D8.

(g) Write a formula in cell B11 to compute the standard deviation. 

(h) Use cells B13 to B18 to document your formulas.

(i) Now we are going to simplify life by using formulas built into the spreadsheet. In cell B21 type “=SUM(B4:B8)”, which means find the sum of numbers in cells B4 to B8. Cell B21 should display the same number as cell B9. In general, you will not know what functions are available and how to write them. In Excel 2010, use the Formulas ribbon and Insert Function to fi nd SUM.

(j) Select cell B22. Go to Insert Function and fi nd AVERAGE. When you type “=AVERAGE(B4:B8)” in cell B22, its value should be the same as B10. (k) For cell B23, fi nd the standard deviation function (“=STDEV(B4:B8)”) and check that the value agrees with cell B11.

Transcribed Image Text:

A в D 1 Computing standard deviation 2 Data = x x-mean (x-mean)^2 4 17.4 18.1 18.2 7 17.9 8 17.6 9 sum = 10 mean = 11 std dev = 12 13 Formulas: B9 = 14 B10 = 15 B11 = 16 C4 = 17 D4 = 18 D9 = 19 20 Calculations using built-in functions: 21 sum = 22 mean - 23 std dev =