Microsoft KB Archive/826400

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

Article ID: 826400

Article Last Modified on 2/14/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2004 for Mac

-



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.



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

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.

Additional query words: XL2007

Keywords: kbinfo KB826400

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.