Generating a Random Number without RAND() in Excel (44738)


The information in this article applies to:

  • Microsoft Excel for Windows 2.0
  • Microsoft Excel for Windows 3.0
  • Microsoft Excel for Windows 4.0
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95
  • Microsoft Excel for Windows 5.0
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.
Modification Type:MinorLast Reviewed:8/15/2005
Keywords:KB44738
©2004 Microsoft Corporation. All rights reserved.