You may notice different values when worksheets that were created in earlier versions of Excel are recalculated in Excel 2003 and later versions of Excel. This article describes the values that are more accurate in Excel 2003 and later versions of Excel and the values that are less accurate.
Microsoft Excel 2004 for Macintosh InformationThe statistical functions in Microsoft Office Excel 2004 for Macintosh were updated 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 later versions of Excel also applies to Excel 2004 for Macintosh.
Microsoft recommends that you read the article about VAR because it explains modifications in the computational procedure and it also provides a worksheet for more extensive experimentation with adding a power of ten to data values. For additional information about VAR, click the following article number to view the article in the Microsoft Knowledge Base:
Create a blank Microsoft Office Excel worksheet, copy the table that follows later in this section, and then select cell A1 in your blank Excel worksheet. Then paste the entries so that the table fills cells A1:I28 in your worksheet.
|Group||Trial||Value||Modified Value||Power of 10|
|1||1||2||=C3+10^$E$2||All Excel versions:|
|1||1||3||=C4+10^$E$2||Var of Value||Trial|
|2||2||8||=C11+10^$E$2||Excel 2002 values with E2 set to 8:|
|2||2||10||=C12+10^$E$2||Var of Modified Value||Trial|
|3||2||8||=C19+10^$E$2||Excel 2003 and later versions of Excel values with E2 set to 8:|
|Var of Modified Value||Trial|
|Values with E2 set to 8:||Group||1||2||Grand Total|
|Excel 2002||Excel 2003 and later versions||1||1||1||4.8|
|group 2||=VAR(C8:C13)||14.4||14||Grand Total||7500000450000010||0||7058824164705910|
|trial 1||=VAR(C2:C4, C8:C10, C14:C16)||4||2.5|
|trial 2||=VAR(C5:C7, C11:C13, C17:C19)||6||6.25|
Note After you paste this table into your new Excel worksheet, click Paste Options, and then click Match Destination Formatting. With the pasted range still selected, use one of the following methods:
- In Microsoft Office Excel 2007, click the Home tab, click Format in the Cells group, and then click AutoFit Column Width.
- In Microsoft Office Excel 2003 and earlier versions of Excel, point to
Column on the Format menu, and then click
The third pivot table shows results from Excel 2003 and later versions of Excel. The good news is that VAR has been correctly upgraded for the interior of the table, cells G22:H24. Values in these six cells agree with corresponding values in the first pivot table as they should. However, something yet unresolved is significantly wrong in the Grand Total row and Grand Total column. Cells E23:E28 show results from computing various VARs by using the Excel 2003 and later versions of Excel VAR function for Modified Values with cell E2 set to 8. These results agree with entries in the Grand Total row and Grand Total column of the first pivot table as they should.
Unfortunately, this author recommends that you ignore values in the Grand Total row and Grand Total column when you use VAR to summarize data in an Excel pivot table. For earlier versions of Excel, all entries in such pivot tables are likely to be correct provided that data values are not so extreme as to cause significant round off problems. To examine the accuracy of any VAR in the table, identify the range of data, Range, of whose VAR you want. For earlier versions of Excel, the correct value is obtained by computing DEVSQ(Range) / (COUNT(Range) – 1). For Excel 2003 and later versions of Excel, the correct value comes from VAR(Range).
The same comments and same performance issues apply to VARP. To verify the accuracy of any VARP in the table in any version, identify the range of data, Range, of whose VARP that you want. For earlier versions of Excel, the correct value is obtained by computing DEVSQ(Range) / COUNT(Range). For Excel 2003 and later versions of Excel, the correct value comes from VARP(Range).
Results in Earlier Versions of ExcelUse of the calculator formula in earlier versions of Excel makes VAR and VARP, STDEV, STDEVP more susceptible to round off errors. However, round off errors are very rarely significant enough that a typical user will notice them. These errors are likely to occur only in extreme situations, particularly when data contains many significant digits but small variations.
Results in Excel 2003 and later versions of ExcelAn improved procedure that involves two passes through the data is correctly implemented for the interior of a pivot table. 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 the number of observations minus 1 for VAR or by the number of observations for VARP. This procedure is generally recommended over the calculator formula to minimize the risk of round off errors.
Unfortunately, this has not been correctly implemented for the Grand Total row and Grand Total column. The author suggests an alternative mechanism for obtaining correct values of these VAR or VARPs.
ConclusionsYou will occasionally see differences when you compare the values of VAR or VARP that are calculated with earlier versions of Excel to values that are calculated with Excel 2003 and later versions of Excel. In these cases, Excel 2003 and later versions of Excel will always be more accurate.
If Excel 2003 and later versions of Excel are used to summarize data with VAR or VARP in a pivot table, you can rest assured that results in the interior of the table are equally accurate or more accurate than in earlier versions of Excel. Users of Excel 2003 and later versions of Excel can ignore results in the Grand Total row and Grand Total column until computational issues with these entries are resolved.
ID do Artigo: 829250 - Última Revisão: 20 de jun de 2014 - Revisão: 1