MrExcel.com - Your One Stop for Excel Tips & Solutions

Bug with Rand() in Excel 2003

December 11, 2003 - by

Do you remember the cool Animated Cannon Chart from our December 2002 challenge? It was programmed by Earl Takasaki. Well, kudos to Earl for discovering a significant bug in Excel 2003 this week. Here is Earl’s note:

You might be interested to know that I believe that I have found a fairly significant bug in EXCEL 2003’s RAND() function. As you know, RAND() should return pseudo-random numbers uniformly distributed in the interval [0,1).

However, repeated calls to it will result in the function “breaking” and it starts to return negative numbers with a funny distribution.

Try this:

Fill 20 full columns of RAND() functions. Now press F9 several times. Soon enough, some of the numbers (about 15%) will go negative. (use conditional formatting to show this more clearly) This did not happen in Excel 2002.

This matters because the RAND() function is used for many simulation and decision models in all types of fields and this kind of error will break all of those models.

Thanks to Earl for discovering this. I did set this up. This worksheet has 1.2 million =RAND() functions. On the 4th recalc, many numbers do come up negative as shown below.

RAND Bug in Excel 2003
RAND Bug in Excel 2003

Here is the good news:

I personally talked to someone on the Excel team at Microsoft on Wednesday December 10, 2003. The problem is being solved and an update will be made available.

You seem to get between 3.5 million and 15 million “good” random numbers before the process goes bad. So - if you are simply using =RAND() to play a couple games of craps during the coffee break, you won’t be in bad shape.

The problem only effects those who have upgraded to Excel 2003. While I think this is a must-have update for the scientific community, not a lot of people will be affected by this bug.

I expect that we will have an update from Microsoft soon. I will post information here when I learn anything about it.