HOW TO: Use RAND() to Generate Randomly Distributed Integers in Excel 2000 (214090)

SUMMARY

This step-by-step article describes how you can use the RAND function to generate randomly sorted, uniformly distributed integers in Microsoft Excel 2000.

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

back to the top

How to Generate Randomly Distributed Integers

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:

1. In a new worksheet, type the following:

      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
   					

2. Select the range A1:B10.
3. On the Data menu, click Sort.
4. In the Sorted by list box, click Column A, and then click OK to sort the range.

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 that the range is sorted, the integers in B1:B10 are re-sorted randomly, which results in a new set.

back to the top