Microsoft KB Archive/169169

= Microsoft Knowledge Base =

How to Automatically Return a Random Value from a List
Last reviewed: May 29, 1997

Article ID: Q169169

The information in this article applies to:


 * Microsoft Excel for Windows 95, version 7.0
 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for the Macintosh, versions 5.0, 5.0a
 * Microsoft Excel 97 for Windows

SUMMARY
By using a combination of the INDEX, INT, ROWS, and RAND functions in a worksheet formula in Microsoft Excel, you can automatically return a random value from a list on a worksheet.

MORE INFORMATION
The following example formula automatically returns a random value from a list on a worksheet

=INDEX(,INT(ROWS(Range)*RAND)-1,1)

where  is the address of the cells that contain the data.

To use the formula, do the following:

  Type the following information into a worksheet: A1: A      A2:  B      A3:  C      A4:  D      A5:  E      A6:  F      A7:  G      A8:  H      A9:  I      A10: J   In cell C1, type the following formula: =INDEX(A1:A10,INT(ROWS(A1:A10)*RAND)-1,1)  To generate a new value in cell C1, press F9 to recalculate the formula.

For additional information, please see the following articles in the Microsoft Knowledge Base:

ARTICLE-ID: Q44738 TITLE    : Generating a Random Number without RAND in Excel

ARTICLE-ID: Q96746 TITLE    : XL: Using RAND to Generate Randomly Distributed Integers