CSCI A113
Lab Notes Three

Fall 2001

Squared deviations and the mean.

We will "prove" today in lab that the sum of the squared deviations of all the scores about their mean is a minimum. In other words, the formula below, in which zeta is an unknown (or variable)

admits a minimum for
Let's prove that (and in the process calculate other things as well).

Here are the steps:

  1. Open up Excel. New worksheet.

  2. Enter these numbers: 1, 2, 3, 2, 4 in cells A1:A5.

    That's our data (the scores).

  3. In E1 write this formula
    =average(a1:a5)
    For me that is 2.4 (the arithmetical mean of these 5 numbers).

  4. Let's calculate the deviations to the mean.

    In B1 write the formula for the first deviation:

    =A1-$E$1
    Notice that the second element has an absolute reference to column E row 1.

    When we paste this formula that will become relevant.

    The first deviation is -1.4 so the formula works fine.

  5. Select cell B1. Drag the lower right corner of the cell to B5. Release mouse button.

    The deviations are calculated.

  6. Select cell B5. The formula inside it should be:
    =A5-$E$1
    Excel updated only the relative components of the formula.

  7. In cell B6 enter this formula
    =sum(b1:b5)
    The value should be 0 (zero).

    We have just verified that the sum of all the deviations about the mean is zero.

  8. Let's square the deviations one by one.

    In cell C1 write this formula

    =b1^2
    The value is 1.96 for me, the square of -1.4 as expected.

  9. Now let's paste this formula throughout.

    Select C1. Drag lower right corner to C5. Release mouse button.

    Squared deviations have been calculated.

  10. Let's finish this first part.

    In cell C6 enter the following formula

    =sum(c1:c5)
    That's the sum of the squared deviations about the mean, and it's 5.2 for me.

  11. In e2 write this formula:
    =count(a1:a5)
    That's 5. I leave it to you to label your spreadsheet nicely.

  12. Calculate the standard deviation in E3:
    =sqrt(c6/e2)
  13. Do it again like this in E4
    =sqrt(sum(c1:c5)/count(c1:c5))
  14. Do it again in E5 as follows
    =stdevp(a1:a5)
  15. Do it again in E6 as follows
    =stdev(a1:a5)
    That should clarify the difference between STDEVP and STDEV.

  16. Now let's work on the main point of this lab.

    In A9 through F9 enter 0, 1, 2, 3, 4, 5.

    We'll calculate the sum of squared deviations around each of these numbers.

  17. Enter this formula in A10:
    =($A1-A$9)^2
  18. Paste this formula to F10 (drag lower right corner to F10).

  19. Select cells A10:F10. Drag lower right corner to F14.

    The cells from A10 to F14 should now contain squared deviations.

  20. Think a bit about it.

  21. Now let's sum the squared deviations.

    In cell A15 enter this formula

    =sum(a10:a14)
  22. Paste this formula through F15.

  23. With A15:F15 selected click the Chart Wizard button.

    Choose "Line" and click "Finish". Still another way would be to

    1. select A10:F10, then
    2. press and hold the Control key, then
    3. select A14:F14, and then
    4. release the Control key. After this
    5. push the Chart Wizard button and
    6. choose the Scatter Plot type of chart.

  24. You're done. Notice the minimum around 2.4 (the arithmetical mean).

  25. You can now change the numbers and see the chart change.

    There will be a new mean, but that's where the minimum will also be.

  26. The lab assignment for next time is to have this worked out.

  27. You can use a longer sequence, with a different range.

    Please let me know if you have any questions or if you need help.

    Last updated: Oct 31, 2001 by Adrian German for A113