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(<Range>,INT(ROWS(Range)*RAND())-1,1)
where <Range> 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
Keywords : kbusage xlformula Last reviewed: May 29, 1997 |