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

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

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.