How to Use Statistical Functions to Find Standard Deviations and Variances in Excel

By Stephen L. Nelson, E. C. Nelson

Excel provides almost a dozen statistical functions for calculating standard deviations and variances. A standard deviation describes dispersion (spread of data) about (around) the data set’s mean. You can kind of think of a standard deviation as an average deviation from the mean. A variance is just the squared standard deviation. You often use variances and standard deviations in other statistical calculations and as arguments to other statistical functions.

STDEV: Standard deviation of a sample

The STDEV function calculates the standard deviation of a sample, a measure of how widely values in a data set vary around the mean — and a common input to other statistical calculations. The function uses the syntax

=STDEV(number1,[number2])

To calculate the standard deviation of the worksheet range A1:A5 using the STDEV function, for example, use the formula

=STDEV(A1:A5)

If the worksheet range holds the values 1, 4, 8, 9 and 11, the function returns the standard deviation value 4.037326.

The STDEV function lets you include up to 255 arguments as inputs; those arguments can be values, cell references, formulas, and range references. The STDEV function ignores logical values, text, and empty cells.

STDEVA: Alternate standard deviation of a sample

The STDEVA function calculates the standard deviation of a sample, but unlike the STDEV function, STDEVA doesn’t ignore the logical values TRUE (which is 1) and FALSE (which is 0). The function uses the syntax

=STDEVA(number1,[number2])

STDEVA arguments, which can number up to 255, can be values, cell references, formulas, and range references.

STDEVP: Standard deviation of a population

The STDEVP function calculates the standard deviation of a population to measure how widely values vary around the mean. The function uses the syntax

=STDEVP(number1,[number2])

To calculate the standard deviation of the worksheet range A1:A5 using the STDEVP function, for example, use the formula

=STDEVP(A1:A5)

If the worksheet range holds the values 1, 4, 8, 9 and 11, the function returns the standard deviation value 3.611094.

The STDEVP function lets you include up to 255 arguments as inputs; the arguments can be values, cell references, formulas, and range references. The STDEV function ignores logical values, text, and empty cells.

STDEVPA: Alternate standard deviation of a population

The STDEVPA function calculates the standard deviation of a population, but unlike the STDEVP function, STDEVPA doesn’t ignore the logical values TRUE (which is 1) and FALSE (which is 0). The function uses the syntax

=STDEVPA(number1,[number2])

STDEVPA arguments, which can number up to 255, can be values, cell references, formulas, and range references.

VAR: Variance of a sample

The VAR function calculates the variance of a sample, another measure of how widely values in a data set vary around the mean. The VAR function uses the syntax

=VAR(number1,[number2])

A standard deviation is calculated by finding the square root of the variance.

To calculate the variance of the worksheet range A1:A5 using the VAR function, for example, use the formula

=VAR(A1:A5)

If the worksheet range holds the values 1, 4, 8, 9 and 11 the function returns the standard deviation value 16.3.

The VAR function lets you include up to 255 arguments as inputs; the arguments can be values, cell references, formulas, and range references. The VAR function ignores logical values, text, and empty cells.

VARA: Alternate variance of a sample

The VARA function calculates the variance of a sample, but unlike the VAR function, VARA doesn’t ignore the logical values TRUE (which is 1) and FALSE (which is 0). The function uses the syntax

=VARA(number1,[number2])

VARA arguments, which can number up to 255, can be values, cell references, formulas, and range references.

VARP: Variance of a population

The VARP function calculates the variance of a population. The function uses the syntax

=VARP(number1,[number2])

To calculate the variance of the worksheet range A1:A5 using the VARP function, for example, use the formula

=VARP(A1:A5)

If the worksheet range holds the values 1, 4, 8, 9 and 11 the function returns the standard deviation value 13.04.

The VARP function lets you include up to 255 arguments as inputs; the arguments can be values, cell references, formulas, and range references. The VARP function ignores logical values, text, and empty cells.

VARPA: Alternate variance of a population

The VARPA function calculates the variance of a population, but unlike the VARP function, VARPA doesn’t ignore the logical values TRUE (which is 1) and FALSE (which is 0). The function uses the syntax

=VARPA(number1,[number2])

VARPA arguments, which can number up to 255, can be values, cell references, formulas, and range references.

COVARIANCE.P and COVARIANCE.S: Covariances

Excel supplies two covariance functions: COVARIANCE.S and COVARIANCE.P. The COVARIANCE.S function calculates the covariance of a sample and the COVARIANCE.P function calculates the covariance of a population. The covariance statistics, then, calculate the average of the products of the deviations between pairs of values and uses the syntax

=COVARIANCE.S(array1,array2)

Or

=COVARIANCE.P(array1,array2)

where array1 is the worksheet range holding the first values in the pair and array2 is the worksheet range holding the second values in the pair.

DEVSQ: Sum of the squared deviations

The DEVSQ function calculates the deviations of values from a mean, squares those deviations, and then adds them up. The function uses the syntax

=DEVSQ(number1,[number2]...)

where number1 and, optionally, number2 are worksheet ranges or arrays that hold your values.