Microsoft KB Archive/44738

= 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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.