The VARA function returns the sample variance for a sample
whose values are contained in an Excel worksheet and whose values are specified
by the arguments to VARA.
Syntax
The following code illustrates the VARA function (where
value1,
value2, and
value3 represent up to 30 value arguments):
VARA(value1, value2, value3, ...)
The most common usage of VARA includes only one value argument
that specifies a range of cells that contain the sample. An example of this is VARA(A1:B100).
Example of usage
The VARA function differs from the VAR 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 VARA, 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 VARPA.
With VAR, cells that contain TRUE, FALSE, or a text string
are ignored. Blank cells are also ignored. These interpretations also hold for
COUNT, AVERAGE, and VARP.
We recommend that you use VAR
instead of VARA unless you are sure that you want TRUE, FALSE, and text strings
to be interpreted as the VARA function interprets them. Most data that you want
to calculate a sample variance for is completely numeric; in those cases, VAR
is appropriate.
To illustrate the difference between VARA and VAR,
create a blank Excel worksheet, copy the following table, and then select cell
A1 in your blank Excel worksheet. Then, paste the entries so that the following table fills cells
A1:D12 in your worksheet.
| 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 this table into a new Excel worksheet, click
Paste Options, and then click
Match Destination
Formatting. With the pasted range still selected, use one of the following procedures, as appropriate for the version of Excel that you are running:
- In Microsoft Office Excel 2007, click the Home tab, click Format in the Cells group, and then click AutoFit Column Width.
- In Excel 2003, point to Column on the
Format menu, and then click
AutoFit Selection.
Cells A1:A8 contain data values
that are used in this example to contrast VARA with VAR. All functions that are
used in cells D3:D10 see the data in A1:A8. VARA 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 VARA in A1:A8 are shown in
B1:B8. The worksheet shows that the value of VARA(A1:A8) in cell D10 is exactly
equal to the value of VAR(B1:B8) in cell D12.
VAR and VARA return
sample variance, and VARP and VARPA return population variance. All these
functions are evaluated in Excel 2003 and in later versions of Excel by first computing the number of data
points and their average, 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 procedure in earlier versions of Excel. The following
article in the Microsoft Knowledge Base provides a worksheet that lets you
to examine cases in which unusual behavior occurs in VAR for earlier versions of
Excel but not for Excel 2003 and for later versions of Excel:
826112 Excel
statistical functions: VAR
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 results. These modifications are also described in this Microsoft
Knowledge Base article.