Categories
Notes Old site

Statistical functions in Excel 2003

There were errors in the implementations of some of the statistical functions in the Analysis ToolPak in Excel 2002 and earlier, which have been corrected in Excel 2003. This means that a workbook developed in an earlier version and recalculated in Excel 2003 may produce different results.

The article gives the impression that it is only in rare cases that the corrections will make any difference. That may be true, but it’s not very comforting for those people that they do affect. One of the errors concerns the calculation of the standard normal cumulative distribution function; the old implementation is basically wrong out in the tail. Another one comes up in functions that involve sums of squares: the old version is inaccurate if there are many significant digits in the data but very little difference in the values. I don’t find it hard to imagine either of those situations coming up in practice.

If you think your calculations are affected, you then have to decide whether to stick with the old version and go for consistency, or upgrade and cope with the differences.

Resources

The following external links are relevant:

Categories
Notes Old site

RAND() in Excel 2003

It appears that a bug crept into the final release of Excel 2003 that wasn’t present in the betas. The RAND() function in Excel 2003 was apparently upgraded so that it produced a better distribution of pseudo-random numbers between 0 and 1. The trouble is that the numbers it produces may be more random, but aren’t always in the correct range; sometimes they are negative. The problem affects both the RAND() and RANDBETWEEN() functions.

Microsoft have now (January 2004) released a hotfix (their term, not mine) that they claim fixes the problem. They also claim that it fixes several other problems, a number of which they had not previously mentioned. Some of these problems cause Excel to quit unexpectedly; at least it’s obvious to the user when this happens (although you may lose your work). Others are more subtle:

  • Sometimes the cells in a range are not actually updated when the range is recalculated.
  • When you use a VBA macro to calculate your worksheet, a custom function from a different worksheet may appear to run.
  • When you create multilevel subtotals for your data in an Excel 2003 worksheet, the totals may appear staggered incorrectly, and may exclude grand totals for some functions.

The hotfix is not downloadable. You have to contact Microsoft and convince them that you need it. Also, the installation process includes editing the registry by hand.

Resources

The following external links are relevant: