• Open the Excel program

Suppose we want to know the average age of college students. We take a sample of five students and obtain the following data 23 20 22 24 26. Lets use Excel to find the mean and the standard deviation.

In cell B4 type AGES

click on cell B5 and type 23

click on cell B6 and type 20

click on cell B7 and type 22

click on cell B8 and type 24

click on cell B9 and type 26

Now we have the first columnn. The second column is the mean, so we have to evaluate mean.

Click on cell B11 and =average( then highlight the data from B5 to B9 and then press enter

click on cell C4 and type MEAN

click on cell C5 and type = and then click on cell B11

click on cell C6 and type = and then click on cell B11

click on cell C7 and type = and then click on cell B11

click on cell C8 and type = and then click on cell B11

click on cell C9 and type = and then click on cell B11

Next column is deviations so click on cell D4 and type ID

click on cell D5 type = then click on cell B5, type -, and then click on C5.

click on cell D6 type = then click on cell B6, type -, and then click on C6.

click on cell D7 type = then click on cell B7 type - and then click on C7.

click on cell D8 type = then click on cell B8 type -, and then click on C8.

click on cell D9 type = then click on cell B9 type -, and then click on C9.

The final column is square deviations so click on cell E4 and type SID

click on cell E5 type = then click on cell D5 and then type ^2

click on cell E6 type = then click on cell D6 and then type ^2

click on cell E7 type = then click on cell D7 and then type ^2

click on cell E8 type = then click on cell D8 and then type ^2

click on cell E9 type = then click on cell D9 and then type ^2

We are ready to compute the variance and the SD now.

click on cell D12 and type VARIANCE

click on cell D13 and type SD

Now do not average since we have a sample and we are not dividing by 5 but by one less.

click on cell E11 and type =sum(E5:E9)

click on cell E12 and type =E11/4

We got the variance.

To get SD we take the square root of variance. The command in Excel for the square root is SQRT

click on cell E13 and type =sqrt(E12)

There is also a short cut to get standard deviation in Excel. If you do not need the table and all you need is SD you may get it directly. You can also do it to check your work.

click on cell F13 and type =STDEV(B5:B9).

Exercises

Suppose you have a sample data set 23, 43, 23, 20, 68, 68, 54, 154, 74, 43, 23, 11, 11

Ex.1 Put your new data set in column H starting with cell H2.

Ex.1 Using Excel, find the mean of this data set. Put your result in cell H15

Ex. 3 Using Excel, evaluate the standard deviation of this new data set. Put your result in cell H16