How can I set up the random selection of a cell from within a ran.

N

nybbac

I want Excel to randomly select one cell from a range of cells, but is it
possible? This would be the equivalent of drawing one slip of paper from
several hundred.
 
R

Ragdyer

An uncomplicated way to do this is to simply add a column filled with the
RAND() function next to your column of choices.
Select both columns, and sort on the Rand() column.
Pick the one at the top, or bottom, as your random selection.

A little more complicated procedure would be to use a formula, referring to
this combination of Rand() and your list, and let this formula randomly pick
an item.
Place your list in an "out of the way" section of your sheet, say Y1:Y10.
In Z1, enter:
=RAND()
and drag down to copy to Z10.

Then, enter this formula wherever you wish:

=INDEX(Y1:Y10,RANK(Z1:Z10,Z1:Z10))

Enter this formula as is, if you're going to use it in Row 1 to 10.
If beyond those rows, you'll have to enter it as an array formula using CSE,
<Ctrl> <Shift> <Enter>.

Now, every time the sheet calculates, or you hit <F9>, you'll get a NEW
random selection.
 
I

IanRoy

Hi, nybbac;
Here's another way, if your range is in one column:
Insert a column to the left of it and fill that column with a numerical
series.
If 1 is the first number in that series, and 400 is the last, then enter
this formula into an empty cell: =RANDBETWEEN(1,400)
If that formula is in, say C1, and your range (including the number series)
is in A1:B400, then enter this formula into another empty cell:
=VLOOKUP(C1,A1:B400,2,FALSE)
RANDBETWEEN will select a random number in your series, and VLOOKUP will
return the data in the cell next to that number. As in the other method, F9
will recalculate.
I wonder if RANDBETWEEN will give me better luck with the lottery. :)
Regards,
IanRoy
 
B

Biff

Hi!

=INDEX(A1:A400,RANDBETWEEN(1,400))

No need for helper columns or a lookup!

Biff
 
R

Ragdyer

Ahhh! Yes ... BUT ... There is the need for the Analysis ToolPak!

How about this one, where there's no need for it:

=INDEX(A1:A400,RAND()*400+1)
 
B

Biff

Yeah, that'll work!

Biff
-----Original Message-----
Ahhh! Yes ... BUT ... There is the need for the Analysis ToolPak!

How about this one, where there's no need for it:

=INDEX(A1:A400,RAND()*400+1)
--
HTH,

RD

---------------------------------------------------------- -----------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------- -----------------



.
Click to expand...
 
Top