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.

Analyzing deviations

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:

Score

Deviation from 84.83 Mean

78

6.83

92

7.17

97

12.17

80

4.83

72

12.83

90

5.17

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.