View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
T. Valko T. Valko is offline
external usenet poster
 
Posts: 15,768
Default Randon selection of a value

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