Standard Deviation Calculations With Microsoft Excel

Author: Stephen L. Nelson, CPA

Excel provides useful statistical functions for finding the standard deviation of a data set. In general, these standard deviation functions retrieve a set of values stored in a worksheet range and then make the expected calculation.

A standard deviation, just to remind readers, is a common measure of describing the spread of observations in a distribution. A standard deviation is related to another statistical measure, the variance. A standard deviation is equal to the square root of the variance.


Standard Deviation of a Sample with the STDEV Function

To find the standard deviation of a sample, ignoring logical values and text, use the STDEV function. This function has the following syntax:

=STDEV(data set range)

For example, to find the standard deviation in the worksheet containing a 10-game sample


of a bowler’s scores stored in the worksheet age B2:B11, you could enter:

=STDEV(B2:B:11)

Standard Deviation of a Sample with the STDEVA Function

To find the standard deviation of a sample, and include cells containing the logical value

TRUE as 1 and cells containing text or the logical value FALSE as 0 (zero), you use the

STDEVA function. This function has the following syntax:

=STDEVA(data set range)

Standard Deviation of a Population with the STDEVP Function

To find the standard deviation of a population, ignoring logical values and text, use the STDEVP function. This function has the following syntax:

=STDEVP(data set range)

Standard Deviation of a Population with the STDEVPA Function

Finally, to find the standard deviation of a population, and include cells containing the logical value TRUE as 1 and cells containing text or the logical value FALSE as 0 (zero), use the STDEVPA function. This function has the following syntax:

=STDEVPA(data set range)

A Final Note About the Data Set Range Argument

One other, final point is worth making: You can include multiple worksheet ranges as your data set range. For example, if you were calculating the standard deviation of a population using the STDEVP function and had your data stored in several different worksheet ranges, your function might look like this:

=STDEVP(B2:B:11,U81:I812,Z100:Z200)


Article Source: http://stephenlnelsoncpa.articlesbase.com/small-business-articles/standard-deviation-calculations-with-microsoft-excel-319906.html