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.