Microsoft KB Archive/96746

= Using RAND to Generate Randomly Distributed Integers =

Article ID: 96746

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q96746




 * Microsoft Excel for Windows 2.0|2.0
 * Microsoft Excel for Windows 2.01|2.01
 * Microsoft Excel for Windows 2.1|2.1
 * Microsoft Excel for Windows 2.10c|2.10c
 * Microsoft Excel for Windows 2.10d|2.10d
 * Microsoft Excel for Windows 3.0|3.0
 * Microsoft Excel for Windows 3.0a|3.0a
 * Microsoft Excel for Windows 4.0|4.0
 * Microsoft Excel for Windows 4.0a|4.0a
 * Microsoft Excel for Windows 4.0c|4.0c
 * Microsoft Excel for Windows 5.0c|5.0c
 * Microsoft Excel for Windows NT 5.0|5.0
 * Microsoft Excel for the Macintosh 2.20|2.20
 * Microsoft Excel for the Macintosh 2.2a|2.2a
 * Microsoft Excel for the Macintosh 3.0|3.0
 * Microsoft Excel for the Macintosh 4.0|4.0
 * Microsoft Excel for the Macintosh 5.0|5.0





SUMMARY
Microsoft Excel does not include a built-in method for generating sets of randomly sorted, uniformly distributed integers. The information in this article describes how you can use the RAND function (which is integral to Microsoft Excel) to create a set of this kind.



MORE INFORMATION
Below is an example of a set of randomly sorted, uniformly distributed integers from 1 to 10:

  10   5   7   4   8   1   6   2   3   9

The set is said to be uniformly distributed because every value in the range occurs exactly once.

To generate such a set of randomly sorted, uniformly distributed integers, follow these steps:

  In a new worksheet, enter the following formulas:

      A1: =RAND       B1:   1 A2: =RAND      B2:   2 A3: =RAND      B3:   3 A4: =RAND      B4:   4 A5: =RAND      B5:   5 A6: =RAND      B6:   6 A7: =RAND      B7:   7 A8: =RAND      B8:   8 A9: =RAND      B9:   9 A10: =RAND      B10: 10  Select the range A1:B10. On the Data menu, click Sort. In the 1st Key box, enter A1 and click OK to sort the range.</ol>

After the sort is complete, cells B1:B10 contain a set of randomly sorted, uniformly distributed integers that range from 1 to 10. Each time the range is sorted, the integers in B1:B10 are resorted randomly, which results in a new set.

<div class="references_section">