MORE INFORMATION
The STDEVPA function returns the population standard
deviation for a population whose values are contained in an Excel worksheet and
specified by the argument(s) to STDEVPA.
Syntax
STDEVPA(value1, value2, value3, ...)
The parameters (value1, value2, value 3,...) are up to 30 value
arguments.
Frequently, STDEVPA includes only one value argument that
specifies a range of cells that contain the sample. For example:
Example Usage
STDEVPA differs from STDEVP only in the way it treats cells in the
data range that contain TRUE, FALSE, or a text string. STDEVPA interprets TRUE
as the value 1 and interprets FALSE as the value 0. It interprets a text string
as the value 0. It ignores blank cells. These interpretations also apply for
the COUNTA, AVERAGEA, and STDEVA functions.
STDEVP ignores blank cells
and cells that contain TRUE, FALSE, or a text string. These interpretations
also apply for the COUNT, AVERAGE, and STDEV functions.
Use STDEVP
instead of STDEVPA unless you are sure that you want the function to interpret
TRUE, FALSE, and text strings as described earlier in this article for STDEVPA.
Most data you want to calculate a population standard deviation for is
completely numeric. Therefore, STDEVP is appropriate.
To illustrate
the difference between STDEVPA and STDEVP, create a blank Excel worksheet, copy
the table below, select cell A1 in your blank Excel worksheet, and then click
Paste on the
Edit menu so that the entries in
the table below fill cells A1:D12 in your worksheet.
Data | 0 | | |
| | | |
6 | 6 | Sample Mean for STDEVP,
STDEV | =AVERAGE(A1:A8) |
4 | 4 | Sample Size for STDEVP,
STDEV | =COUNT(A1:A8) |
2 | 2 | STDEVP | =STDEVP(A1:A8) |
1 | 1 | STDEV | =STDEV(A1:A8) |
7 | 7 | Sample Mean for STDEVPA,
STDEVA | =AVERAGEA(A1:A8) |
TRUE | 1 | Sample 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 into your new Excel worksheet, click
the
Paste Options button, and then click
Match
Destination Formatting. With the pasted range still selected, point to
Column on the
Format menu, and then click
AutoFit Selection.
In this example, cells A1:A8
contain data values that contrast STDEVPA with STDEVP. All the functions that
are used in cells D3:D10 refer to the data in A1:A8. STDEVPA treats the text
string in cell A1 as the value 0, the numeric values in A3:A7 as themselves,
and the value TRUE in A8 as 1. The values that are used for STDEVPA in A1:A8
appear in B1:B8. The worksheet shows that the value of STDEVPA(A1:A8) in cell
D9 is exactly equal to the value of STDEVP(B1:B8) in cell D11.
STDEVP
and STDEVPA return population standard deviation, whereas STDEV and STDEVA
return sample standard deviation. In all versions of Excel, a value is
calculated first for VAR, VARA, VARP, or VARPA. The square root of this value
is returned (respectively) for STDEV, STDEVA, STDEVP, or STDEVPA. To evaluate
VAR, VARA, VARP, and VARPA, Excel 2003 calculates the number of data points and
their average, and then calculates the sum of squared deviations of data values
from this average. This sum of squared deviations is the numerator of the
fraction that is used to evaluate VAR, VARA, VARP, and VARPA. The denominator
for VARP and VARPA is the number of data points. The denominator for VAR and
VARA is one less than the number of data points.
To calculate each of
these four functions, Excel 2003 uses a procedure that differs from and
improves upon the procedure that earlier versions of Excel use. The article for
STDEV gives a worksheet that permits you to examine cases where unusual
behavior occurs in STDEV for earlier versions of Excel, but not for Excel 2003.
However, 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 the results. The
articles for STDEV and VAR also describe these
modifications.
For additional
information, click the following article number to view the article in the
Microsoft Knowledge Base:
826349
Excel Statistical Functions: STDEV
826112 Excel Statistical Functions: VAR
For more
information about STDEVPA, click
Microsoft Excel Help on the
Help menu, type
stdevpa in the
Search for box in the Assistance pane, and then click
Start searching to view the topic.