"Random selection between two cells"

  • Thread starter Thread starter JMRamsey
  • Start date Start date
J

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
 
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
 
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
 
=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)
 
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.
 
Dana said:
Another option to extract a random cell from A1:A10 might be something
like
this:

=OFFSET(A1,RANDBETWEEN(0,9),0,1,1)
Click to expand...

..or not using addin functions....

=INDEX(A1:A10,RAND()*10+1)
 
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
 
Back
Top