Microsoft KB Archive/44738

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
Knowledge Base


Generating a Random Number without RAND() in Excel

Article ID: 44738

Article Last Modified on 11/16/2006



APPLIES TO

  • Microsoft Excel 2.0 Standard Edition
  • Microsoft Excel 3.0 Standard Edition
  • Microsoft Excel 4.0 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition



This article was previously published under Q44738

SUMMARY

Microsoft Excel allows the generation of random numbers. The function RAND() follows the same pattern every time it is used, but alternate methods are available to generate the random numbers.

MORE INFORMATION

The following formula calculates a random number between 0 and 1 that does not follow any pattern:

   =(NOW()*100000 - INT(NOW()*100000))
                



This formula uses the serial value that Microsoft Excel uses to keep track of time. The values in the 10E-6 position and beyond change so rapidly that they have cycled through a complete set (0 through 9) and more by the time Microsoft Excel finishes a single calculation cycle. Hence, shifting the value of NOW() six digits to the left of the decimal (NOW()*100000) and then subtracting the integer portion of the same number (INT(NOW()*100000)) leaves only the portion to the right of the decimal point, which changes rapidly enough so that execution of a Calculate Now command cannot be guessed. This value also happens to be in the same format used by RAND() (that is, a decimal number from 0 to 1). If necessary, 100000 can be changed to a larger or different value.

An alternative method of changing the RAND function is to add RANDOMIZE=1 [Microsoft Excel] to

WIN.INI if using Excel version 2.x
EXCEL.INI if using Excel version 3.0
EXCEL4.INI if using Excel version 4.0


This change allows Microsoft Excel to use the system clock to change the seed value.

Keywords: KB44738