What is VAR and VARP improvements and Data Consolidation in Excel?

[Flaco]

Hi friends,
I am studying about the statistical functions that are used in Excel. I have just went through the VAR and VARP improvements, but I didn't get the actual use of that, since it was not explained properly. Also i have not understood about the data consolidation in Excel. So thought that you guys can help me on this topic (as usual).!! Please tell me what is VAR and VARP improvements and Data Consolidation in Excel? Help me soon.

[Bigga Lexx]

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 enhancements for VARs and VARP function when used to consolidate data. In extreme cases, you may notice different values when worksheets that were created in previous versions of Excel are recalculated in Excel 2003 and in later versions of Excel. This problem also occurs with functions STDEV and STDEVP because they calculate SQRT (VAR) and SQRT (VARP), respectively.

[Zenon]

Since, you have not told about your operating system, I am describing about the Mac OS. The statistical functions in Microsoft Excel 2004 for Macintosh were updated using the same algorithms Excel 2003 and later versions of Excel. The information in this article describes how a function or how a function was modified for Excel 2003 and later versions of Excel also applies to Excel 2004 for Macintosh. Hope that you (or somebody) finds this useful.

[chroma]

Sections of the VAR function and VARP show how a two-pass procedure has replaced one-pass "calculator formula" for calculating these measures. With infinite precision arithmetic, both procedures return the same results and the formula takes into half time as the two pass procedure. However, with finite precision arithmetic, rounding errors can become a factor. With two pass procedure in Excel 2003 and in later versions of Excel, the rounding errors in extreme cases are unlikely. However, you may notice these improvements, in fact, for most datasets, there are too few significant figures in the data lead to different results between the formula and procedure two assists.

[ThoMas321]

The VAR function and VARP are always more accurate in Excel 2003 and in later versions of Excel than in earlier versions of Excel, but the differences between VAR and VARP later versions of Excel and versions previous Excel generally noticeable only when data values contains several significant figures, but have small variations. Using the formula in earlier versions makes the function and VARP VAR, STDEV and STDEVP are more sensitive to round - off errors. However, rounding errors that are large enough to be able to occur only in severe cases, particularly when data contains many significant figures but small variations note.

[Glenny]

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

1. The number of observations minus 1 for the VAR function
2. The number of observations for VARP

This procedure reduces the risk of rounding errors as compared to the formula. When you compare the values of the VAR, VARP, STDEV and STDEVP are calculated by earlier versions of Excel values calculated using Excel 2003 and later versions of Excel, you can sometimes see differences. In this case, the values for Excel 2003 and later versions of Excel are always more accurate. In general, however, there is no difference between values in different versions of Excel.