You are currently offline, waiting for your internet to reconnect

`VAR(value1, value2, value3, ...)`

Data | |||

6 | Sample Mean | =AVERAGE(A3:A8) | |

4 | Sample Size | =Count(A3:A8) | |

2 | VAR | =VAR(A3:A8) | |

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.

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.

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

`VAR(values)`

`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

`VAR(values)`

`(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:

- VAR
- VARP
- STDEV
- STDEVP
- DVAR
- DVARP
- DSTDEV
- DSTDEVP
- FORECAST
- SLOPE
- INTERCEPT
- PEARSON
- RSQ
- STEYX

Properties

Article ID: 826112 - Last Review: 09/19/2011 00:07:00 - Revision: 4.0

Microsoft Office Excel 2007, Microsoft Excel 2004 for Mac

- kbformula kbexpertisebeginner kbinfo KB826112