Question

Spreadsheet for standard deviation. 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 B4to B8contain the data ( xvalues) whose mean and standard deviation we will compute.

(b) Write a formula in cell B9to compute the sum of numbers in B4to B8.

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

(d) Write a formula in cell C4to compute (- mean), where xis in cellB4 and the mean is in cell B10. Use Fill Down to compute values in cells C5to C8.

(e) Write a formula in cellto compute the square of the value in cell. Use Fill Down to compute values in cellsto.

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

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

(h) Use cells B13to B18to document your formulas.

(i) Now we are going to simplify life by using formulas built into the spreadsheet. In cell B21type $\mathbf{\text{'}\text{'}}\mathbf{=}\mathbf{SUM}\left(B4:B8\right)\mathbf{\text{'}\text{'}}$which means find the sum of numbers in cells B4to B8. Cell B21should 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 find SUM.

(j) Select cellB22. Go to Insert Function and find AVERAGE. When you type "=AVERAGE(B4:B8)" in cell B22, its value should be the same asB10.

(k) For cellB23, find the standard deviation function$\left(=\mathrm{STDEV}{\left(B4:B8\right)}^n\right)$and check that the value agrees with cell B11.

Short answer

The spreadsheets are:

Step-by-step solution

Definition of standard deviation and mean.

• Arithmetic mean: By dividing the amount of measured values by the number of measurements n, the arithmetic mean$\overline{x}$ is determined. The average is often referred to as the "mean."

Mean:$\overline{x}=\frac{{\displaystyle \underset{i}{\sum x_i}}}{n}$

• Standard deviation:The standard deviation is a measurement of how tightly the data cluster around the mean.

The standard deviation (s) is given by the formula:

role="math" localid="1663561684565" $s=\sqrt{\frac{{\displaystyle \sum _i{\left(x_i-\overline{x}\right)}^2}}{n-1}}$

Standard deviation of the mean of sets of n values is:

${\sigma }_n=\frac{\theta }{\sqrt{n}}$

Find a spreadsheet to compute themeanand standard deviationof a column of numbers in two different ways.

The total and standard deviation for the column of numbers in figure 1 are computed using the techniques below.

(a) Fill in the values from cells B4 to B8 (x values) in a spreadsheet identical to the one shown in Figure 1.

(b) By putting a formula in cell B9, the total of integers in B 4 to B 8 is calculated.

(c) In cell B10, a formula is used to get the mean of the numbers.

(d) In cell C4, write a formula to compute ( x - mean). Fill in the numbers in cells C5 through C8 with fill down.

(e) In cell D4, provide a formula to compute the square of the value in cell C4. Fill in the numbers in cells D5 through D8 with fill down.

(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 calculate the standard deviation (h) Using cells B13 through B18, record the formulae.

(i) In cell B21, enter the formula "=SUM(B4:B8)"

(j) Go to the insert function and type AVERAGE in cell B22.

(k) Go to insert function and type STANDARD DEVIATION in cell B23.

Figure 1: Spreadsheet Calculation of Mean and Standard Deviation:

Figure 2 shows the computation of mean and standard deviation using a spreadsheet after following the methods outlined above.

The mean and standard deviation of a particular column of data were determined in two distinct methods using a spreadsheet.