Microsoft Office Tutorials and References
In Depth Information
Deviating from the Middle
4.Sumupthesquares.
5.Dividethesumofthesquaresbythenumberofitemsinthesample,
minus 1.
A sample is a selected set of values taken from the population. A sample is
easier to work with. For example, any statistical results found on 1,000 sales
transactions probably would return the same, or close to the same, results if
run on the entire population of 10,000 transactions.
Note that the last step differs depending on whether the VAR.S or VAR.P
function is used. VAR.S uses the number of items, minus 1, as the denominator.
VAR.P uses the number of items.
Figure 9-4 shows these steps in calculating a variance without using Excel’s
built-in function for the task. Column B has a handful of values. Column C shows
the deviation of each figure from the mean of the values. The mean, which
equals 7.8, is never actually shown. Instead, the mean is calculated within the
formula that computes the difference. For example, cell C8 has this formula:
=B8-AVERAGE($B$4:$B$8)
Column D squares the values in Column C. This is an easy calculation. Here
are the contents of cell D8: =C8^2. Finally, the sum of the squared deviations
is divided by the number of items, less one item. The formula in cell D12 is
=SUM(D4:D8)/(COUNT(B4:B8)-1).
Figure 9-4:
Calculating
variance
from the
mean.
Functions that do the work: VAR.S and VAR.P
Now that you know how to create a variance the textbook way, you can
forget it all! Here I show the mathematical steps so you could understand
what happens, but Excel provides the VAR.S and VAR.P functions to do all
the grunge work for you.
 
Search JabSto ::




Custom Search