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.
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.
1)
It is given that the given data set is 23, 20, 22, 24, 26.
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 3 images
