Description of the STDEVA statistical function in Excel

The purpose of this article is to illustrate the difference between the STDEVA function in Excel 2003 and in later versions of Microsoft Excel and the closely related function of STDEV. This article also points out any possible differences between the results of the STDEVA function for Excel 2003 and for later versions of Excel and the results of STDEVA for earlier versions of Excel.

Microsoft Excel 2004 for Macintosh information

The statistical functions in Excel 2004 for Mac were updated by using the same algorithms that were used to update the statistical functions in Excel 2003 and in later versions of Excel. Any information in this article that describes how a function works or how a function was modified for Excel 2003 and in later versions of Excel also applies to Excel 2004 for Mac.

The STDEVA function returns the population standard deviation for a population whose values are contained in an Excel worksheet and whose values are specified by the arguments in STDEVA.

Syntax

The following code illustrates the STDEVA function (where value1, value2, and value3 represent up to 30 value arguments): The most common usage of STDEVA includes only one value argument that specifies a range of cells that contain the sample (for example, STDEVA[A1:B100]).

Example of usage

The STDEVA function differs from the STDEV function only in the way that it treats cells in the data range that contain TRUE or FALSE or that contain a text string.

With STDEVA, TRUE is interpreted as the value 1; FALSE is interpreted as 0; any text string is interpreted as 0; and any blank cell is ignored. These interpretations also hold for COUNTA, AVERAGEA, and STDEVA.

With STDEV, cells that contain TRUE, FALSE, or a text string are ignored. Blank cells are also ignored. These interpretations also hold for COUNT, AVERAGE, STDEV and STDEVP.

Microsoft recommends that you use STDEV instead of STDEVA unless you are sure that you want TRUE, FALSE, and the text strings to be interpreted as the STDEVA function interprets them. Most of the data that you want to calculate a population standard deviation for is completely numeric; in those cases, STDEV is appropriate.

To illustrate the difference between STDEVA and STDEV, create a blank Excel worksheet, copy the following table, and then select cell A1 in your blank Excel worksheet. On the Edit menu, click Paste.

Note In Excel 2007, click Paste in the Clipboard group on the Home tab.

The entries in the following table fill cells A1:D12 in your worksheet.After you paste this table in your new Excel worksheet, click Paste Options, and then click Match Destination Formatting.

With the pasted range still selected, on the Format menu, point to Column, and then click AutoFit Selection.

Note In Excel 2007, with the pasted range of cells selected, click Format in the Cells group on the Home tab, and then click AutoFit Column Width.

Cells A1:A8 contain data values that are used in this example to contrast STDEVA with STDEV. All functions that are used in cells D3:D10 refer to the data in A1:A8. STDEVA treats the text string in cell A1 as the value 0, the numeric values in A3:A7 as numeric values, and the value TRUE in A8 as 1. The values that are used for STDEVA in A1:A8 are shown in B1:B8. The worksheet shows that the value of STDEVA(A1:A8) in cell D10 is exactly equal to the value of STDEV(B1:B8) in cell D12.

STDEV and STDEVA return sample standard deviation, and STDEVP and STDEVPA return population standard deviation. In all versions of Excel, a value is computed first for VAR, VARA, VARP, or VARPA; the square root of this value is returned (respectively) for STDEV, STDEVA, STDEVP, or STDEVPA. All these functions are evaluated in Excel 2003 and in later versions of Excel by first computing the number of data points and their averages, and then by computing the sum of the squared deviations of data values from this average.

This sum of the squared deviations is the numerator of the fraction that is used to evaluate VAR, VARA, VARP, and VARPA. The denominator for VAR and VARA is one less than the number of data points. The denominator for VARP and VARPA is the number of data points.

Each of these four functions is computed by a procedure in Excel 2003 and in later versions of Excel that differs from and improves on the procedurethat was used in earlier versions of Excel.

The article for STDEV provides a worksheet that lets you examine cases in which unusual behavior occurs in STDEV for earlier versions of Excel, but not for Excel 2003 and for later versions of Excel. It must be emphasized that such cases are likely to occur only in extreme situations.

Procedures for STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA have all been modified in the same way to improve the numeric stability of results. These modifications are also described in the articles for STDEV and VAR.

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base: