Excel statistical functions: VARA

Summary

This article describes the difference between the VARA function in Microsoft Office Excel 2003 and in later versions of Excel and the closely related VAR function. This article also points out the possible differences between the results of the VARA function for Excel 2003 and for later versions of Excel and the results of VARA in earlier versions of Microsoft Excel.

Microsoft Excel 2004 for Macintosh Information

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

More Information

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.
Data0
66Sample Mean for VARP, VAR=AVERAGE(A1:A8)
44Sample Size for VARP, VAR=COUNT(A1:A8)
22VARP=VARP(A1:A8)
11VAR=VAR(A1:A8)
77Sample Mean for VARPA, VARA=AVERAGEA(A1:A8)
TRUE1Sample 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)
Note 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.
Egenskaper

Artikel-id: 826371 – senaste granskning 29 apr. 2008 – revision: 1

Feedback