Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How can I set up the random selection of a cell from within a ran.
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. |
#2
|
|||
|
|||
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "nybbac" wrote in message ... 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. |
#3
|
|||
|
|||
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 "nybbac" wrote: 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. |
#4
|
|||
|
|||
Hi!
=INDEX(A1:A400,RANDBETWEEN(1,400)) No need for helper columns or a lookup! Biff -----Original Message----- 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 "nybbac" wrote: 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. . |
#5
|
|||
|
|||
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 ! --------------------------------------------------------------------------- "Biff" wrote in message ... Hi! =INDEX(A1:A400,RANDBETWEEN(1,400)) No need for helper columns or a lookup! Biff -----Original Message----- 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 "nybbac" wrote: 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. . |
#6
|
|||
|
|||
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 ! ---------------------------------------------------------- ----------------- "Biff" wrote in message ... Hi! =INDEX(A1:A400,RANDBETWEEN(1,400)) No need for helper columns or a lookup! Biff -----Original Message----- 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 "nybbac" wrote: 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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
How do I get one cell to record the time another cell was changed. | Excel Discussion (Misc queries) | |||
Display actual contents of cell | Excel Discussion (Misc queries) | |||
copy a cell value not its function | Excel Discussion (Misc queries) |