Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to random pick a cell from more than one column
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to random pick a cell from more than one column
On Jan 6, 1:15*pm, fruitchunk
wrote: 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)),RANDBETW EEN(1,COUNTA (A2:F2))) Jay |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to random pick a cell from more than one column
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to random pick a cell from more than one column
"Shane Devenshire" wrote: 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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pick a Random name from a list. | Excel Discussion (Misc queries) | |||
Random pick of a cell | Excel Discussion (Misc queries) | |||
Can excel pick a random cell from a column? | Excel Worksheet Functions | |||
Pick a Random name from a list. | Excel Worksheet Functions | |||
random pick names from the list | Excel Discussion (Misc queries) |