Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Description of the difference between the VARPA function and the VARP function in Excel


View products that this article applies to.

Summary

This article describes the difference between the VARPA function and the closely related function, VARP in Microsoft Office Excel 2007 and Microsoft Office Excel 2003. This article also points out possible differences between the results of the VARPA function for Excel 2007 and Excel 2003 and the results of the VARPA function in earlier versions of Microsoft Excel.

Microsoft Excel 2004 for Macintosh Information

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

↑ Back to the top


More information

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
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 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.

↑ Back to the top


Keywords: KB826400, kbinfo

↑ Back to the top

Article Info
Article ID : 826400
Revision : 4
Created on : 2/14/2007
Published on : 2/14/2007
Exists online : False
Views : 426