Microsoft Excel 2004 for Mac informationThe statistical functions in Excel 2004 for Mac were updated by using the same algorithms that were used to update the statistical functions in Excel 2003 and in 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 Mac.
SyntaxThe following code illustrates the VAR function (where value1, value2, and value3 represent up to 30 value arguments):
VAR(value1, value2, value3, ...)
Example of usageCreate a blank Excel worksheet, copy the following table, select cell A1 in your blank Excel worksheet, and then paste the entries so that the following table fills cells A1:D17 in your worksheet.
|1||Pre-Excel 2003 VAR v1||=(D4*SUMSQ(A3:A8) - SUM(A3:A8)^2)/(D4*(D4 - 1))|
|3||Pre-Excel 2003 VAR v2||=(SUMSQ(A3:A8) - (SUM(A3:A8)^2)/D4)/(D4 - 1)|
|5||VAR in Excel 2003 and in later versions of Excel||=DEVSQ(A3:A8)/(D4 - 1)|
|Modified Data||Power of 10 Added to Data||1|
|=A3 + 10^$D$10||Sample Mean||=AVERAGE(A12:A17)|
|=A4 + 10^$D$10||Sample Size||=COUNT(A12:A17)|
|=A5 + 10^$D$10||VAR||=VAR(A12:A17)|
|=A6 + 10^$D$10||Pre-Excel 2003 VAR v1||=(D13*SUMSQ(A12:A17) - SUM(A12:A17)^2)/(D13*(D13 - 1))|
|=A7 + 10^$D$10||Pre-Excel 2003 VAR v2||=(SUMSQ(A12:A17) - (SUM(A12:A17)^2)/D13)/(D13 - 1)|
|=A8 + 10^$D$10||VAR in Excel 2003 and in later versions of Excel||=DEVSQ(A12:A17)/(D13 - 1)|
- 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.
Cell D5 contains the value of VAR for your current version of Excel. If you use Excel 2003 or a later version of Excel, this value should agree with the value in cell D8 because cell D8 shows the value of VAR for Excel 2003 and for later versions of Excel, regardless of which version of Excel you use. Cells D6 and D7 show two approximations of the value of VAR that were computed by earlier versions of Excel. The formula in cell D6 is the formula that is shown in the Help file for Microsoft Excel 2002 and earlier versions of Excel.
In this example, all versions return the value 3.5000000. There are no computational problems that may cause differences in VAR between versions of Excel.
Rows 10 to 17 let you experiment with modified data by adding a constant (in this case, a power of 10 is added) to each data point. Adding a constant to each data point should not affect the value of sample variance.
If you change the value in cell D10 (for example, you change the value to 1, 2, 3, 4, 5, 6, or 7), the revised data values are in cells A12:A17 and all versions of VAR are well-behaved in these seven cases.
However, if you try the values 8, 9, and 10 in cell D10, the value for Excel 2003 and for later versions of Excel remains at 3.5 (as it should), while the values for Excel 2002 and for earlier versions of Excel change, even though they should remain constant at 3.5. This behavior would not occur if computations could be performed with infinite precision.
Earlier versions of Excel show answers that are not correct in these cases because the effects of round-off errors are more profound with the computational formula that is used in these versions. However, the cases in this example are rather extreme.
Results in earlier versions of ExcelIn extreme cases where there are many significant digits in the data that have little variation, the old computational formula causes results that are not accurate. Earlier versions of Excel use a one-pass process through the data to compute the sum of squares of the data values, the sum of the data values, and the count of the data values (sample size). These quantities are then combined in the computational formula that is provided in the Help files in earlier versions of Excel.
Results in Excel 2003 and in later versions of ExcelThe procedure that is used in Excel 2003 and in later versions of Excel uses a two-pass process through the data.
First, the sum and count of the data values are computed. The sample mean, or average, is computed from these values. In the second pass, the squared difference between each data point and the sample mean is found and these squared differences are summed.
In the numeric examples, even with a high power of 10 in cell D10, these squared differences are not affected, and the results of the second pass are independent of the entry in cell D10. Therefore, the results in Excel 2003 and in later versions of Excel are more stable numerically.
ConclusionsReplacing a one-pass process with a two-pass process guarantees better numeric performance of VAR in Excel 2003 and in later versions of Excel. Results that you obtain in Excel 2003 and in later versions of Excel are never less accurate than results that you obtain in earlier versions of Excel.
In most practical examples, however, you are not likely to see a difference between results in later versions of Excel and results in earlier versions of Excel because typical data is not likely to exhibit the kind of unusual behavior that this example illustrates. Numeric instability is most likely to appear in earlier versions of Excel when the data contains many significant digits that have relatively little variation.
If you use an earlier version of Excel, and if you want to determine whether switching to Excel 2003 or a later version of Excel will make a difference, compare the results of
DEVSQ(values)/(COUNT(values) – 1)
If you use Excel 2003 or a later version of Excel, and if you want to determine whether the computed value of VAR(values) has changed from the value that you found in an earlier version of Excel, compare
(SUMSQ(values) - (SUM(values)^2)/COUNT(values))/(COUNT(values) - 1)
The procedure of finding the sum of squared deviations about a sample mean by finding the sample mean, computing each squared deviation, and then summing the squared deviations is more accurate than the following procedure:
- Find the sum of squares of all observations, the sample size, and the sum of all observations.
- Compute the sum of squares of all observations minus ((sum of all observations)^2)/sample size).
There are many other functions that have been improved for Excel 2003 and for later versions of Excel. These functions were improved by replacing the one-pass process with the two-pass process that finds the sample mean on the first pass and computes the sum of squared deviations on the second pass.
The following functions have been improved in Excel 2003 and in later versions of Excel: