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.
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
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