Excel statistical functions: VAR and VARP improvements and data consolidation

Microsoft Office Excel 2003 and later versions of Excel calculate the statistical functions VAR and VARP more accurately than earlier versions of Excel. Microsoft made the same algorithmic improvements for VAR and VARP when they are used to consolidate data.

In extreme cases, you may notice different values when worksheets that were created in earlier versions of Excel are recalculated in Excel 2003 and in later versions of Excel. This behavior also occurs with the STDEV and STDEVP functions because they calculate SQRT(VAR) and SQRT(VARP), respectively.

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 and for later versions of Excel also applies to Excel 2004 for Macintosh.

The articles on VAR and VARP discuss how a two-pass procedure has replaced the one-pass "calculator formula" for calculating these measures. With infinite precision arithmetic, both procedures return the same results, and the calculator formula takes half as much time as the two-pass procedure.

However, with finite precision arithmetic, round-off errors can become a factor. With the two-pass procedure in Excel 2003 and in later versions of Excel, round-off errors in extreme situations are much less likely. However, you may not notice these improvements because, for most data sets, there are too few significant digits in the data to cause different results between the calculator formula and the two-pass procedure.

VAR and VARP are always more accurate in Excel 2003 and in later versions of Excel than in earlier versions of Excel, but differences in VAR and VARP between later versions of Excel and earlier versions of Excel are typically noticeable only if data values contain many significant digits, yet have small variation.

The following tables illustrate a situation in which earlier versions of Excel return incorrect values. Adding the same constant to every value in a data set should not affect the value of VAR. In the table, the Modified Value column contains the same entries as the Value column with the constant 10^8 added. To repeat the experiment, follow these steps:

1. Open a new workbook, and then add a fourth worksheet named "Consolidation."
2. Copy and paste the following four tables into Sheet1, Sheet2, Sheet3, and Consolidation. When you do this, select cell A1 on each sheet before the paste operation. When you do this, the tables fill A1:D2, A1:B2, A1:B2, and A1:B8, respectively.
3. If you are using an earlier version of Excel, you can experiment more by changing the value of the power of 10 in Sheet1 cell D1. When you do this, you notice that VAR is well-behaved when this value is less than or equal to seven.

Sheet1

Sheet2

Sheet3

Consolidation

VAR of Value in cell B2 of the Consolidation sheet is 1. VAR of Modified Value in cell B3 of the Consolidation sheet is 1. The value 1 is the correct value if you use Excel 2003 or a later version of Excel. However, 0 is an incorrect value if you use an earlier version of Excel.

The article about VAR discusses modifications in the calculation. The article also gives you a worksheet for more extensive experimentation with adding a power of ten to data values.

For more information about VAR, click the following article number to view the article in the Microsoft Knowledge Base:

Results in earlier versions of Excel

Use of the calculator formula in earlier versions makes VAR and VARP, STDEV, and STDEVP are more susceptible to round-off errors. However, round-off errors that are significant enough for you to notice them occur only in extreme situations, particularly when data contains many significant digits but small variations.

Results in Excel 2003 and in later versions of Excel

The procedure that Excel 2003 and later versions of Excel use involves two passes through the data. On the first pass, the sample mean is calculated. On the second pass, the sum of squared deviations about this sample mean is calculated. This sum is then divided by one of the following values:

• The number of observations minus one for VAR
• The number of observations for VARP

This procedure minimizes the risk of round-off errors as compared to the calculator formula.

Conclusions

When you compare the values of VAR, VARP, STDEV, and STDEVP that are calculated by using earlier versions of Excel to values that are calculated by using Excel 2003 and later versions of Excel, you may occasionally see differences. In these cases, the values for Excel 2003 and for later versions of Excel are always more accurate. Typically, however, there is no difference between the values in the different versions of Excel.