Excel tools to demonstrate

Try it! For the data set in column A, which extends well below row 10, find the 10 largest values and 10 smallest values in columns D and E. Use the values of k in column C. (Scroll to the right for the answers.)

STDEV, VAR Functions

Excel has a number of other statistical functions, including those for measuring the amount of variability in a set of numbers. The most widely used measures of variability are variance and standard deviation, and Excel implements these with the VAR and STDEV functions. (These functions calculate the sample variance and standard deviation. There are similar functions, VARP and STDEVP, for calculating the population variance and standard deviation. For large data sets—over 100 values, say—the sample and population measures are practically the same.)

The variance is essentially the average of the squared deviations from the average, and the standard deviation is the square root of the variance. Each measures variability, but standard deviation is easier to understand because it is expressed in the same units as the data—dollars, for example. Besides, many data sets have the property that about 68% of the data are within 1 standard deviation of the average, about 95% are within 2 standard deviations of the average, and virtually all of the data are within 3 standard deviations of the average.

To use the VAR function:

Enter the formula =VAR(datarange).

This is equivalent to =(STDEV(datarange))^2, the square of the standard deviation

To use the STDEV function:

Enter the formula =STDEV(datarange).

This is equivalent to =SQRT(VAR(datarange)), the square root of the variance.

Try it! Find the average, variance, and standard deviation of the data in column A. Then use COUNTIF to find the percentage of values that are within 1 standard deviation of the average. Is it about 68%? (Scroll to the right to see the answers.)