Excel Statistical Functions: VARA (826371)

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): 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 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 Microsoft 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.

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)

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, 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 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 by first computing the number of data points and their average, 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 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 for Excel 2003: 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 the previous Microsoft Knowledge Base article.

For more information about VARA, click Microsoft Office Excel Help on the Help menu, type vara in the Search for box in the Assistance pane, and then click Start searching to view the topic.