Hi. I have a spread sheet with 90 values in cells L1 through L91.

I am using =int(rand()*91+1) to generate random integers from 1 to 91. I
then want cell C3 to display the contents of L** depending on which random
number was called (so if it give me 10, C3 should display the contents of
L10).

Could anyone tell me how is best to do this. I can't use nested IF
statements as I have too much information in the list.

Thanks.

=Indirect("L"&int(rand()*92))

"suitcase" wrote:

Hi. I have a spread sheet with 90 values in cells L1 through L91.

I am using =int(rand()*91+1) to generate random integers from 1 to 91. I
then want cell C3 to display the contents of L** depending on which random
number was called (so if it give me 10, C3 should display the contents of
L10).

Could anyone tell me how is best to do this. I can't use nested IF
statements as I have too much information in the list.

Thanks.

Just some thoughts....

=int(rand()*91+1)

Using that formula there is the high probability (almost a certainty!) of
getting duplicates.

You not really gaining anything by puting that formula in 91 cells and then
using a separate formula to randomly pick one of those cells.

Just use a single cell with the above formula and the net effect will be the
same.

--
Biff
Microsoft Excel MVP


"suitcase" wrote in message
...
Hi. I have a spread sheet with 90 values in cells L1 through L91.

I am using =int(rand()*91+1) to generate random integers from 1 to 91. I
then want cell C3 to display the contents of L** depending on which random
number was called (so if it give me 10, C3 should display the contents of
L10).

Could anyone tell me how is best to do this. I can't use nested IF
statements as I have too much information in the list.

Thanks.

Hi,

Excel has a RANDBETWEEN(1,91) function which returns integers between the
lower and upper bound. It is part of the ATP - you can attach it by choosing
Tools, Add-ins, and check Analysis ToolPak.

To return the value at that location you have many options

=INDEX(L1:L91,RANDBETWEEN(1,91))
or
=OFFSET(A1,RANDBETWEEN(1,91)-1,)
or
=INDIRECT("L"&RANDBETWEEN(1,91))
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"suitcase" wrote:

Hi. I have a spread sheet with 90 values in cells L1 through L91.

I am using =int(rand()*91+1) to generate random integers from 1 to 91. I
then want cell C3 to display the contents of L** depending on which random
number was called (so if it give me 10, C3 should display the contents of
L10).

Could anyone tell me how is best to do this. I can't use nested IF
statements as I have too much information in the list.

Thanks.