Microsoft Office Tutorials and References

In Depth Information

**Deviating from the Middle**

Here’s how to use the AVEDEV function:

1. Enter a list of numerical values.

2. Position the cursor in the cell where you want the
average deviation

to appear.

3. Enter
=AVEDEV(
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.

The AVEDEV function averages the
absolute
deviations — in other words,

negative deviations
(where the data point is less than the mean) are converted to

positive values for the calculation. For example, a value of 10 has a deviation

of –40 from a mean of 50: 10 – 50 = –40. However, AVEDEV uses the absolute

value of the deviation, 40, instead of –40.

The variance, explained earlier in the chapter, serves as the basis for a

common statistical value called the
standard deviation.
Technically speaking,

the standard deviation is the square root of the variance. Variance is

calculated by
squaring
deviations from the mean.

The variance and the standard deviation are both valid measurements of

deviation. However, the variance can be a confusing number to work with. In

Figure 9-4, the variance was calculated to be 17.7 for a group of values whose

range is just 11 (14 – 3). How can a range that is only a size of 11 show a

variance of 17.7? Well, it does, as shown earlier in Figure 9-4.

This oddity is removed when using the standard deviation. The reversing of

the squaring brings the result back to the range of the data. The standard

deviation value fits inside the range of the sample values. In addition, you’ll

find the standard deviation is more commonly used than the variance in

statistical analyses. Excel has a standard deviation formula — STDEV.P. This is

how you use it:

1. Enter a list of numerical values.

2. Position the cursor in the cell where you want the
standard deviation

to appear.

3. Enter
=STDEV.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.