Thursday, July 14, 2005

Random Number Generation

The function RAND() can be used to generate a random number between 0 and 1.
However this isn't always necessarily useful on its own. Today I was asked to produce a dual random number generator that looked at two data sets and selected randomly from each.
The first had 5 items and the second had 6.

To solve this I created two lookup tables with the first or index column being the numbers (ranked in order) and the second column being the items. I named these ranges for ease of use in formulas.

Then I created a formula.
=CONCATENATE(VLOOKUP(INT((upper1 - lower1 + 1) * RAND() + lower1),table1,2,FALSE),"-",,VLOOKUP(INT((upper2 - lower2 + 1) * RAND() + lower2),table2,2,FALSE))

Note: upper and lower are the highest and lowest calues (can be a cell link) for each data set.

This is essentially the same formula twice, once for each data set, and joined using the concatenate function.


Posted by: Jethro | 12:01 AM |