The VARPA function returns the population variance for a
population whose values are contained in an Excel worksheet and whose values
are specified by the arguments to VARPA.
Syntax
The following code illustrates the VARPA function (where
value1,
value2, and
value3 represent up to 30 value arguments):
VARPA(value1, value2, value3,�)
The most common usage of VARPA includes only one value argument
that specifies a range of cells that contain the sample. An example of this is
VARPA(A1:B100)).
Example Usage
The VARPA function differs from the VARP function only in the way
that it treats cells in the data range that contain TRUE, FALSE, or a text
string. With the VARPA function, 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 VARA. With
the VARP function, cells that contain TRUE, FALSE, or a text string are
ignored. Blank cells are also ignored. These interpretations also hold for
COUNT, AVERAGE, and VAR.
We recommend that you use VARP instead of
VARPA unless you are sure that you want the function to interpret TRUE, FALSE,
and text strings to be interpreted as the VARPA function interprets them. Most
data that you want to calculate a population variance for is completely
numeric; in those cases, VARP is appropriate.
To illustrate the
difference between VARPA and VARP, create a blank Excel worksheet, copy the
following table, and then select cell A1 in your blank Excel worksheet. Press
CTRL + V so that the entries in the table fill cells
A1:D12 in your worksheet.
Collapse this tableExpand this table
| Data | 0 | | |
| | | |
| 6 | 6 | Sample Mean for VARP,
VAR | =AVERAGE(A1:A8) |
| 4 | 4 | Sample Size for VARP,
VAR | =COUNT(A1:A8) |
| 2 | 2 | VARP | =VARP(A1:A8) |
| 1 | 1 | VAR | =VAR(A1:A8) |
| 7 | 7 | Sample Mean for VARPA,
VARA | =AVERAGEA(A1:A8) |
| True | 1 | Sample Size for VARPA,
VARA | =COUNTA(A1:A8) |
| | VARPA | =VARPA(A1:A8) |
| | VARA | =VARA(A1:A8) |
| | VARP for Column B | =VARP(B1:B8) |
| | VAR for Column B | =VAR(B1:B8) |
After you paste the table into your new Excel worksheet, click
Paste Options, and then click
Match Destination
Formatting. With the pasted range still selected, in
Excel 2003, on the
Format menu, point
to
Column, and then click
AutoFit Selection.
In Excel 2007, click the
Home tab, click
Format in the
Cells group, and then click
AutoFit Column
Width.
Cells A1:A8 contain data
values that are used in this example to contrast VARPA with VARP. All functions
that are used in cells D3:D10 see the data in cells A1:A8. VARPA treats the
text string in cell A1 as the value 0, the numeric values in cells A3:A7 as
numeric values, and the value TRUE in cell A8 as 1. The values that are used
for VARPA in cells A1:A8 are shown in cells B1:B8. The worksheet shows that the
value of VARPA(A1:A8) in cell D9 is exactly equal to the value of VARP(B1:B8)
in cell D11.
The VARP and VARPA functions return population variance,
and the VAR and VARA functions return sample variance. All these functions are
evaluated in Excel 2003 by first computing the number of data points and their
average, and then computing 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 VARP, VARPA, VAR, and VARA. 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.
Each of these four functions is
computed by a procedure in Excel 2007 and
Excel 2003 that differs from and improves on the
procedure in earlier versions of Excel. The following article in the Microsoft
Knowledge Base provides a worksheet that lets you to examine cases where
unusual behavior occurs in VAR for earlier versions of Excel but not in Excel
2003:
826112�
Excel Statistical Functions: VARA
It must be emphasized, however, that such cases are
likely to occur only in extreme situations. Procedures for VAR, VARA, VARP, and
VARPA have all been modified in the same way to improve the numeric stability
of the results. These modifications are also described in the previous
Microsoft Knowledge Base article.