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.