Excel Statistical Functions: STDEVA (826409)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac

SUMMARY

The purpose of this article is to illustrate the difference between the STDEVA function in Excel 2003 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 the results of STDEVA in earlier versions of Microsoft 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 Microsoft Office Excel 2003. Any information in this article that describes how a function works or how a function was modified for Excel 2003 also applies to Excel 2004 for Mac.

MORE INFORMATION

The STDEVA function returns the population standard deviation for a population whose values are contained in a 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):

STDEVA(value1, value2, value3,.)

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 Usage

The STDEVA function only differs from the STDEV function 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 so that the entries in the following table fill cells A1:D12 in your worksheet.

Data0
66Sample Mean for STDEVP, STDEV=AVERAGE(A1:A8)
44Sample Size for STDEVP, STDEV=COUNT(A1:A8)
22STDEVP=STDEVP(A1:A8)
11STDEV=STDEV(A1:A8)
77Sample Mean for STDEVPA, STDEVA=AVERAGEA(A1:A8)
TRUE1Sample Size for STDEVPA, STDEVA=COUNTA(A1:A8)
STDEVPA=STDEVPA(A1:A8)
STDEVA=STDEVA(A1:A8)
STDEVP for Column B=STDEVP(B1:B8)
STDEV for Column B=STDEV(B1:B8)


Note 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.

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 by first computing the number of data points and their averages, and then 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 that differs from and improves on the procedure in earlier versions of Excel. The article for STDEV provides a worksheet that permits you to examine cases where unusual behavior occurs in STDEV for earlier versions of Excel, but not for Excel 2003. 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 additional information about Excel Statistical Functions: VAR, click the following article numbers to view the articles in the Microsoft Knowledge Base:

826112 Excel Statistical Functions: VAR

826349 Excel Statistical Functions: STDEV

For more information about STDEVA, click Microsoft Office Excel Help on the Help menu, type stdev in the Search for box in the Assistance pane, and then click Start searching to view the topic.
Modification Type:MinorLast Reviewed:1/11/2006
Keywords:kbinfo KB826409 kbAudEndUser
©2004 Microsoft Corporation. All rights reserved.