"Random selection between two cells"

[JMRamsey]

Does anyone know how to randomly select a number from a column. I would
like to randomly select from a given field, not a random number between
two numbers. Thanks

 

[SteveG]

Do you mean return a random column number?

=RANDBETWEEN(COLUMN(A1),COLUMN(H20))

This re-calcs using F9 or anytime the workbook auto calcs.


Does that help?

Steve

 

[SteveG]

If you want to randomly retreive a value from a given range of cells say

A1:A10 you could try this.

=CHOOSE(RANDBETWEEN(1,10),A1,A2,A3,A4,A5,A6,A7,A8,A9,A10)

This works for up to 29 values. The RANDBETWEEN picks a random number
from the total and assigns the index of the CHOOSE function which then
retrieves the value in the corresponding cell. The cells you retrieve
data from do not need to be in order.

HTH

Steve

 

[Dana DeLouis]

=CHOOSE(RANDBETWEEN(1,10),A1,A2,A3,A4,A5,A6,A7,A8,A9,A10)

Another option to extract a random cell from A1:A10 might be something like
this:

=OFFSET(A1,RANDBETWEEN(0,9),0,1,1)

 

[Mark Lincoln]

If you know how many rows you have to choose from, you can use OFFSET.
This formula is in A1 and assumes there are values in A2 through A20
(19 rows):

=OFFSET(A1,RANDBETWEEN(1,19),0)

Use F9 to get a new choice.

If you have more than 29 rows to choose from this will work where the
CHOOSE function will not.

 

[Tushar Mehta]

If you are looking for alternatives to those already suggested, you might
want to see
Random Selection
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[daddylonglegs]

Another option to extract a random cell from A1:A10 might be something
like
this:

=OFFSET(A1,RANDBETWEEN(0,9),0,1,1)

..or not using addin functions....

=INDEX(A1:A10,RAND()*10+1)

 

[Dana DeLouis]

=INDEX(A1:A10,RAND()*10+1)

Yep. Much nicer! Thanks. :>)

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"daddylonglegs" <[email protected]>
wrote in message

 

[Guest]

You could use something like this:

=LARGE(A1:A10,RANBETWEEN(1,10))

This returns the nth largest number from the given range, where n is
randomly selected from 1 to the number of fields in your range.

HTH,
Elkar