Can excel pick a random cell from a column?
 

I'm dong a random giveaway and have a huge list of name in an excel sheet. Is
there a way for excel to pick a random cell instead of printing, cutting and
pulling from a hat. I thought I did this ions ago but maybe it was a dream???

Can excel pick a random cell from a column?
 

Try something like this:

Put this table of values in A1:C6
198 208 2.0
209 219 2.5
220 229 3.0
230 240 3.5
241 247 4.0
248 258 4.5

Next....
put this formula in F1
=VLOOKUP(E1,$A$1:$C$6,3,1)
(it will initially display #N/A)

Last...enter a number in E1...The formula in F1 will assign it a value.

Example:
E1: 215
F1: displays 2.5

Is that something you can work with?
Post back with any questions.
***********
Regards,
Ron

XL2002, WinXP


"Helene deChappe" wrote:

I'm dong a random giveaway and have a huge list of name in an excel sheet. Is
there a way for excel to pick a random cell instead of printing, cutting and
pulling from a hat. I thought I did this ions ago but maybe it was a dream???

Can excel pick a random cell from a column?
 

YIKES! That post has NOTHING to do with your issue. It was meant for another
thread.

I apologize for the mispost.
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try something like this:

Put this table of values in A1:C6
198 208 2.0
209 219 2.5
220 229 3.0
230 240 3.5
241 247 4.0
248 258 4.5

Next....
put this formula in F1
=VLOOKUP(E1,$A$1:$C$6,3,1)
(it will initially display #N/A)

Last...enter a number in E1...The formula in F1 will assign it a value.

Example:
E1: 215
F1: displays 2.5

Is that something you can work with?
Post back with any questions.
***********
Regards,
Ron

XL2002, WinXP


"Helene deChappe" wrote:

I'm dong a random giveaway and have a huge list of name in an excel sheet. Is
there a way for excel to pick a random cell instead of printing, cutting and
pulling from a hat. I thought I did this ions ago but maybe it was a dream???

Can excel pick a random cell from a column?
 

=INDIRECT("A"&INT(RAND()*7)+1)

replace A with the column containing the list of names, and replace 7 with
the number of names in the list.

Hit F9 to get a new name.

"Helene deChappe" wrote:

I'm dong a random giveaway and have a huge list of name in an excel sheet. Is
there a way for excel to pick a random cell instead of printing, cutting and
pulling from a hat. I thought I did this ions ago but maybe it was a dream???

Can excel pick a random cell from a column?
 

Helene deChappe wrote:
I'm dong a random giveaway and have a huge list of name in an excel sheet. Is
there a way for excel to pick a random cell instead of printing, cutting and
pulling from a hat.

Try one of the following, depending on whether the names are in a
column (first one) or in a row (second one):

=offset(B3,10*rand(),0)

=offset(B3,0,10*rand())

Replace "B3" with the reference to the cell with the first name.
Replace "10" with the number of names -- or use count(B3:B12), where
"B3:B12" is the range that contains all names.

Note that if you use F9 to select more than one name (or if you put the
formula in multiple cells for that purpose), the same name might be
selected multiple times. Also note that if you save and later re-open
the worksheet, you will likely find a different name selected.

If those are problems for you, feel free to post back to ask for
solutions.

Can excel pick a random cell from a column?
 

Another option, using RANDBETWEEN

Assuming names listed in col A
Then in say, B1:
=INDEX(A:A,RANDBETWEEN(1,COUNTA(A:A)))
will return a random name from col A
Press F9 to re-generate

Note that RANDBETWEEN requires the Analysis Toolpak (ATP)
to be installed and activated.

Check the "Analysis Toolpak" box (via Tools Add-Ins)

Chip Pearson's page has details on the ATP at:
http://www.cpearson.com/excel/ATP.htm
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Helene deChappe" wrote:
I'm dong a random giveaway and have a huge list of name in an excel sheet. Is
there a way for excel to pick a random cell instead of printing, cutting and
pulling from a hat. I thought I did this ions ago but maybe it was a dream???

Can excel pick a random cell from a column?
 

or you could use RAND() instead which doesn't require Analysis ToolPak, i.e.

=INDEX(A:A,RAND()*COUNTA(A:A)+1)

"Max" wrote:

Another option, using RANDBETWEEN

Assuming names listed in col A
Then in say, B1:
=INDEX(A:A,RANDBETWEEN(1,COUNTA(A:A)))
will return a random name from col A
Press F9 to re-generate

Note that RANDBETWEEN requires the Analysis Toolpak (ATP)
to be installed and activated.

Check the "Analysis Toolpak" box (via Tools Add-Ins)

Chip Pearson's page has details on the ATP at:
http://www.cpearson.com/excel/ATP.htm
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Helene deChappe" wrote:
I'm dong a random giveaway and have a huge list of name in an excel sheet. Is
there a way for excel to pick a random cell instead of printing, cutting and
pulling from a hat. I thought I did this ions ago but maybe it was a dream???