Calculating the Variance and the Standard Deviation with the Excel Spreadsheet Program

We can use the Excel spreadsheet program to assist us in calculating the variance and the standard deviation for either a sample or a population.

Let us say that we have the quiz scores for nine students in a mathematics class for their first four quizzes. In a previous lesson we learned how to use the spreadsheet program to calculate the means for each of the quizzes. The data for these nine pupils and the means which are calculated could appear in a spreadsheet as follows:

Using the Office 98 Version of Excel (in either the Windows or the Macintosh versions), starting with the worksheet containing the quiz scores and their means, we can find the population and the sample values for the variance and the standard deviation for each quiz by proceeding as follows:

• Using Excel to find the population variance for the quiz scores
  1. In the Quiz Scores for Students in Mathematics Class worksheet, Click on cell A14 and enter the words Pop Variance
  2. Click on the cell B14 and click on which is the paste function icon.
  3. In the Paste Function window which appears select Statistical under Function Category: and VARP under Function name:, and click OK.
  4. In the VARP box that appears enter B3:B11 and click OK. The Population Variance for Quiz 1 (based on the scores in cells B3 through B11) appears in cell B14.
  5. To format this value to three decimal places, click on , the Increase Decimal icon, three times, and observe that the Population Variance for Quiz 1 appears as 6.000
  6. Copy this same formula to the other three quizzes by clicking on cell B14 and dragging over to cell E14. Select Fill from the Edit menu and then with the mouse button depressed slide to the right and select Right. The Population Variance, formatted to three decimal places now appears for each of the quizes.
  
  
• Using Excel to find the population standard deviation for the quiz scores
  1. In the Quiz Scores for Students in Mathematics Class worksheet, Click on cell A15 and enter the words Pop SD
  2. Click on the cell B15 and click on which is the paste function icon.
  3. In the Paste Function window which appears select Statistical under Function Category: and STDEVP under Function name:, and click OK.
  4. In the STDEVP box that appears enter B3:B11 and click OK. The Population Standard Deviation for Quiz 1 (based on the scores in cells B3 through B11) appears in cell B15.
  5. To format this value to three decimal places, click on , the Decrease Decimal icon, five times, and observe that the Population Standard Deviation for Quiz 1 appears as 2.449
  6. Copy this same formula to the other three quizzes by clicking on cell B15 and dragging over to cell E15. Select Fill from the Edit menu and then with the mouse button depressed slide to the right and select Right. The Population Standard Deviation, formatted to three decimal places now appears for each of the quizzes.
  
  
• Using Excel to find the sample variance for the quiz scores
  1. In the Quiz Scores for Students in Mathematics Class worksheet, Click on cell A16 and enter the words Sample Var
  2. Click on the cell B16 and click on which is the paste function icon.
  3. In the Paste Function window which appears select Statistical under Function Category: and VAR under Function name:, and click OK.
  4. In the VAR box that appears enter B3:B11 and click OK. The Sample Variance for Quiz 1 (based on the scores in cells B3 through B11) appears in cell B16.
  5. To format this value to three decimal places, click on , the Increase Decimal icon, one time, and observe that the Sample Variance for Quiz 1 appears as 6.750
  6. Copy this same formula to the other three quizzes by clicking on cell B16 and dragging over to cell E16. Select Fill from the Edit menu and then with the mouse button depressed slide to the right and select Right. The Sample Variance, formatted to three decimal places now appears for each of the quizzes.
  
  
• Using Excel to find the sample standard deviation for the quiz scores
  1. In the Quiz Scores for Students in Mathematics Class worksheet, Click on cell A17 and enter the words Sample SD
  2. Click on the cell B17 and click on which is the paste function icon.
  3. In the Paste Function window which appears select Statistical under Function Category: and STDEV under Function name:, and click OK.
  4. In the STDEV box that appears enter B3:B11 and click OK. The SAMPLE SD for Quiz 1 (based on the scores in cells B3 through B11) appears in cell B17.
  5. To format this value to three decimal places, click on , the Decrease Decimal icon, five times, and observe that the Sample SD for Quiz 1 appears as 2.598
  6. Copy this same formula to the other three quizzes by clicking on cell B17 and dragging over to cell E17. Select Fill from the Edit menu and then with the mouse button depressed slide to the right and select Right. The Sample Standard Deviation, formatted to three decimal places now appears for each of the quizes.
  
  

The completed worksheet shows the means, the variances, and the standard deviations, for all four quizzes and should look something like the following: