Randon selection of a value

[JohnB]

Is there a function for randomly selecting a value from a list of values?

 

[Sheeloo]

You can use =Rand() to generate a random number then use that to find a value
from the list...

If you have 10 values in the list and your list is in C1:C10 then use
=INDEX(C1:C10,ROUND(RAND()*10,0),1)

 

[T. Valko]

ROUND(RAND()*10,0)

That will generate random numbers from 0 to 10 and could lead to an error or
a non-random result depending on where the formula is entered.

Try this:

ROUND(RAND()*9,0)+1

Or this:

RANDBETWEEN(1,10)

This one requires the Analysis ToolPak add-in be installed if using Excel
versions prior to Excel 2007.

 

[Bernd P]

Hello,

That formula will return the 10th list value only with a likelihood of
1/20 and with the same likelihood the erroneous index 0 and return an
error, I think.

To avoid this:
=INDEX(C1:C10,INT(RAND()*10+1),1)

Regards,
Bernd

 

[James Silverton]

T. wrote on Sat, 15 Nov 2008 13:36:06 -0500:

That will generate random numbers from 0 to 10 and could lead to an
error or a non-random result depending on where the
formula is entered.

Try this:

Or this:

This one requires the Analysis ToolPak add-in be installed if using
Excel versions prior to Excel 2007.

Yes, you beat me to it and I had forgotten that RANDBETWEEN required the
Analysis ToolPak. Didn't everyone install it? The earlier random number
routines were not very good but, unless you have money riding on it, are
usually satisfactory. I'm still using Excel 2002 but I implemented a
quite fast Box-Muller algorithm for normal distributions that seemed to
pass most tests.

"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com"> wrote in message

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[T. Valko]

RANDBETWEEN required the Analysis ToolPak. Didn't everyone install it?

Judging from the number of questions about #NAME? errors I would say a lot
of users haven't installed the ATP!

 

[Bernd P]

Hello Biff,

I would not suggest to install the ATPI, I would install Excel 2007
instead

Regards,
Bernd