Generating a Random Number without RAND() in Excel

Last reviewed: November 2, 1994
Article ID: Q44738

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0

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.

KBCategory: kbusage
KBSubcategory:
Additional words: 5.00 2.0 2.00 2.01 2.1 2.10 3.0 3.00 4.0 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.