Microsoft Office Tutorials and References

In Depth Information

**Using sample and population statistical functions**

Using sample and population statistical functions

Variance and standard deviation are statistical measurements of the dispersion of a group,

or population, of values. The standard deviation is the square root of the variance. As a rule,

about 68 percent of a normally distributed population falls within one standard deviation

of the mean, and about 95 percent falls within two standard deviations. A large standard

deviation indicates that the population is widely dispersed from the mean; a small standard

deviation indicates that the population is tightly packed around the mean.

The VAR and STDEV function families compute the variance and standard deviation of

the numbers in a range of cells. Before you make these calculations, you must determine

whether those values represent the total population or only a representative sample of

that population. The VAR.S and STDEV.S functions assume that the values represent only a

sample of the total population, while the VAR.P and STDEV.P functions assume that the

values represent the total population. The A versions—VARA, VARPA, STDEVA, and STDEVPA—

include numeric text entries and logical values in their calculations, while the others do not.

Calculating sample statistics: VAR.S and STDEV.S

The VAR.S and STDEV.S functions compute variance and standard deviation, assuming that

their arguments represent only a sample of the total population. These functions take the

arguments
number1
,
number2
, and so on, and they accept up to 255 arguments. The

worksheet in Figure 17-2 shows exam scores for five students and assumes that the scores in

cells B4:E8 represent only a part of the total population.

Figure 17-2
Here, the VAR.S and STDEV.S functions measure the dispersion of sample exam

scores.

You can find the VAR.xlsx file with the other examples on the companion website.

The formula in cell I4 =VAR.S(B4:E8) calculates the variance for this sample group of test

scores. The formula in cell I5 =STDEV.S(B4:E8) calculates the standard deviation.