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


--
JMRamsey
------------------------------------------------------------------------
JMRamsey's Profile: http://www.excelforum.com/member.php...o&userid=32799
View this thread: http://www.excelforum.com/showthread...hreadid=526212

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
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=526212

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=526212

=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)

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


"SteveG" wrote in
message ...

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile:
http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=526212

Dana DeLouis Wrote:
=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)


..or not using addin functions....

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=526212

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

Yep. Much nicer! Thanks. :)

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


"daddylonglegs"
wrote in message
news:daddylonglegs.257a1b_1143247802.0715@excelfor um-nospam.com...

Dana DeLouis Wrote:
=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)


.or not using addin functions....

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=526212

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

--
Regards,

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

In article ,
says...

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


--
JMRamsey
------------------------------------------------------------------------
JMRamsey's Profile: http://www.excelforum.com/member.php...o&userid=32799
View this thread: http://www.excelforum.com/showthread...hreadid=526212

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


"JMRamsey" wrote:


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


--
JMRamsey
------------------------------------------------------------------------
JMRamsey's Profile: http://www.excelforum.com/member.php...o&userid=32799
View this thread: http://www.excelforum.com/showthread...hreadid=526212