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

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)

"JohnB" wrote:

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

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.

--
Biff
Microsoft Excel MVP


"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in
message ...
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)

"JohnB" wrote:

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

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

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.

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
...
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)

"JohnB" wrote:

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



--

James Silverton
Potomac, Maryland

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

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!

--
Biff
Microsoft Excel MVP


"James Silverton" wrote in message
...
T. wrote on Sat, 15 Nov 2008 13:36:06 -0500:

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.

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
...
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)

"JohnB" wrote:

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



--

James Silverton
Potomac, Maryland

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

Hello Biff,

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

Regards,
Bernd

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