In Excel 2003 and 2007 the number generation algorithm has been significantly "improved" in order to provide more "reliable" random distribution when large number of random values are required. However there is bug in the new system that makes the RAND function (and RANDBETWEEN) spontaneously churn out negative numbers. When the RAND function is called a large number of times, some cells begin to start returning negative "random" numbers even though the RAND function is only supposed to return values between 0 and 1. Unfortunately the problem is not easily fixed as the negative numbers are not well distributed (so using the ABS() function just buries the problem into a poor distribution even by psudo-random number standards).

How to see the RAND() function bug in Excel 2003: In cell A1 Type " =RAND() " Select this cell and fill the spread sheet cells from "A1" to "S1200" Enter the coordinate of the last data cell "B2" Press the "F9" key to recalculate the worksheet several times.

The only way to temporarily prevent this from happening is to close Excel and reopen it. A fix has been released by microsoft for excel 2003 and it is expected to be fixed before excel 2007 ships out for public release. [Excel 2003 rand bug fix (and more)] [MS Office Excel 2003 download fix] Please note that his bug will most likely never effect the average user of Excel. It is only when you call the RAND or RANDBETWEEN function more than a million times does the function breakdown and return negative numbers. However it is a good idea to regularly download patches for your Microsoft software and make sure you have the most up to date service pack or update installed.