Microsoft Office Tutorials and References

In Depth Information

**Using AVEDEV, DEVSQ, VAR.S, and STDEV.S to Calculate Dispersion**

The table in F7:G12 shows the actual behavior of the significanceargument.

The values in column G show the PERCENTRANK.INC of cell B2 to the sig-

nificance in column F. You can see that the student ranked at the 99.6th per-

centile is in the 90th percentile when the significance is 1. A significance of 1

would assign 30 records to be at the 90th percentile.

Figure 14.10
also shows the difference between PERCENTRANK.INC and

PERCENTRANK.EXC. Although Christopher Moon is at the 0.0th percentile in

cell C302 using PERCENTRANK.INC, the .EXC version never assigns a value

to the 0.0 percentile. Cell D302 shows a percentile of 0.3% for the lowest

score in a 300 row data set.

Using

Using
AVEDEV

AVEDEV
,,
DEVSQ

DEVSQ
,,
VAR.S

VAR.S
, and

, and
STDEV.S

STDEV.S
to Calculate Dispersion

to Calculate Dispersion

Functions such as AVERAGE tell you about the center of a range of data.

Seeing the center is not always the entire picture. The other key element of

descriptive statistics is dispersion. If you have a population, the average

height might be x. If you look at dispersion, you can find out if every member

of the population is tightly grouped around the average or if there is wide

variability.

Here are several measures of dispersion:

•
Average deviation is calculated by measuring the absolute differ-

ence of each data point from the mean and then averaging these values.

Suppose the values in a population are 12, 14, 16, 18, and 20. The mean

is 16. Average deviation adds up 4, 2, 0, 2, and 4 and divides the total

by 5 to yield 2.4. Excel offers AVEDEV to calculate this.

•
Average deviation is not perfect. Suppose you have another popula-

tion of 11, 15, 16, 17, and 21. Again, the mean is 16. The average deviation

averages 5, 1, 0, 1, and 5 to yield an average deviation of 2.4. If you

want to measure how far from the mean the points range, you can add

up the squares of each deviation. In this case, the square of 5 is 25,

and it indicates more dispersion than the square of 4. Excel offers

DEVSQ to calculate the squares of each deviation.

•
Variance is a common measurement of dispersion. It averages the

square deviations to come up with the variance of a data set. Here is

the one odd thing about variance: Suppose you have 20 measurements,

and they represent the entire population (for example, the 20 fish in an

aquarium). In this case, you divide DEVSQ by 20 to calculate the vari-

ance. You use VAR.P in Excel to do this. However, if your 20 values