Microsoft Office Tutorials and References
In Depth Information
Deviating from the Middle
The VAR.S function calculates the variance of a sample of 20 values. The
VAR.P function calculates the variance of the full population of 40 values.
VAR.P is entered in the same fashion as VAR.S. Here’s how:
1. Enter a list of numerical values.
Any mix of numbers will do.
2. Position the cursor in the cell where you want the variance to appear.
3. Enter =VAR.P( to start the function.
4. Drag the pointer over the list, or enter the address of the range.
5. Enter a ) and press the Enter key.
Often, finding the mean is an adequate measure of a sample of data.
Sometimes the mean is not enough — you also want to know the average
deviation from the mean. That is, finding the average of how far individual
values differ from the mean of the sample. For example, you may need to
know the average score on a test and also how far the scores, on average,
differ from the mean. Average deviation is another way to specify variance.
Here’s an example:
Deviation from 84.83 Mean
The mean of this sample of values is 84.83. Use the AVERAGE function, if you
want to double-check. Each individual value deviates somewhat from the
mean. For example, 92 has a deviation value of 7.17 from the mean. A simple
equation proves this: 92 – 84.83 = 7.17.
If you use the AVERAGE function to get the mean of the deviations, you have
the average deviation. It’s even easier than that, though. Excel provides the
AVEDEV function for this very purpose! AVEDEV calculates the mean and
averages the deviations all in one step.