How to random pick a cell from more than one column

[fruitchunk]

I know how to pick a random cell from one column
=INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1)

but I need to pick a random cell from more than one column, I tried this:
=INDEX($A:$F,RANDBETWEEN(1,COUNTA($A:$F)),1)
but it doesn't work.

I tried searching the other posts but I couldn't figure it out.
Please help, Thanks.

 

[jlclyde]

I know how to pick a random cell from one column
=INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1)

but I need to pick a random cell from more than one column, I tried this:
=INDEX($A:$F,RANDBETWEEN(1,COUNTA($A:$F)),1)
but it doesn't work.

I tried searching the other posts but I couldn't figure it out.
Please help, Thanks.

This will not pick the cell for you but it will tell you what the
value is in that cell. The counta I was guessing were column and row
headers.

=INDEX(A2:F7,RANDBETWEEN(2,COUNTA(A2:A7)),RANDBETWEEN(1,COUNTA
(A2:F2)))

Jay

 

[Shane Devenshire]

Hi,

Assuming you are using 2003 or earlier, a faster version of the first
formula would be

=INDEX($A:$A,RANDBETWEEN(1,65536),1)

and the second formula would be

=INDEX($A:$F,RANDBETWEEN(1,65536),RANDBETWEEN(1,6))

Also using COUNTA will make the function non-random if there are empty cells
in the data. So for example, if A1:A10 is empty but the rest of the cells
filled, then counta give 65526 which means that the items in A65527 and on
never get picked.

Now for those who are going to add the RANDBETWEEN is not really random, yes
I know.

Another solution is to use the ATP Sampling tool. You can sample from any
range and return 1 entry or more. The ATP is attached by choosing Tools,
Add-ins and checking Analysis ToolPak. Then choose the command Tools, Data
Analysis, Sampling...

If this helps, please click the yes button.

Cheers,
Shane Devenshire

 

[fruitchunk]

:
Another solution is to use the ATP Sampling tool. You can sample from any
range and return 1 entry or more. The ATP is attached by choosing Tools,
Add-ins and checking Analysis ToolPak. Then choose the command Tools, Data
Analysis, Sampling...

I have Excel 2007, I tried to use the Data Analysis Sampling, but i get an
error:
"Input range contains non-numeric data"