| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pick data from an array
How do I randomly pick a set of numbers out of an array? I have 5 columns
and 10 rows of data and I want to randomly pick 5 numbers out of this data set how do I do that? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pick data from an array
one way:
Put in a cell and copy down to fill 5 cells: =LARGE($A$1:$E$10,INT(RAND()*50+1)) HTH "Winston Williams" wrote: How do I randomly pick a set of numbers out of an array? I have 5 columns and 10 rows of data and I want to randomly pick 5 numbers out of this data set how do I do that? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pick data from an array
How should this formula be adjusted if the amount of data increases?
"Toppers" wrote: one way: Put in a cell and copy down to fill 5 cells: =LARGE($A$1:$E$10,INT(RAND()*50+1)) HTH "Winston Williams" wrote: How do I randomly pick a set of numbers out of an array? I have 5 columns and 10 rows of data and I want to randomly pick 5 numbers out of this data set how do I do that? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pick data from an array
Change the range and change 50 to number of cells in range
e.g if range is 8 columns and 15 rows (120 cells in total ) then 50 becomes 120: =LARGE($A$1:$H$15,INT(RAND()*120+1)) "Winston Williams" wrote: How should this formula be adjusted if the amount of data increases? "Toppers" wrote: one way: Put in a cell and copy down to fill 5 cells: =LARGE($A$1:$E$10,INT(RAND()*50+1)) HTH "Winston Williams" wrote: How do I randomly pick a set of numbers out of an array? I have 5 columns and 10 rows of data and I want to randomly pick 5 numbers out of this data set how do I do that? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pick data from an array
Change the range and change 50 to number of cells in range
e.g if range is 8 columns and 15 rows (120 cells in total ) then 50 becomes 120: =LARGE($A$1:$H$15,INT(RAND()*120+1)) And if you don't want to have to worry about the cell count, use this variation and just change both ranges each time you make a change in the number of cells included... =LARGE($A$1:$H$15,INT(RAND()*COUNTIF($A$1:$H$15,"< """)+1)) Rick |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pick data from an array
WOW!!!
Thank you very much. This user group is phenomenal. "Rick Rothstein (MVP - VB)" wrote: Change the range and change 50 to number of cells in range e.g if range is 8 columns and 15 rows (120 cells in total ) then 50 becomes 120: =LARGE($A$1:$H$15,INT(RAND()*120+1)) And if you don't want to have to worry about the cell count, use this variation and just change both ranges each time you make a change in the number of cells included... =LARGE($A$1:$H$15,INT(RAND()*COUNTIF($A$1:$H$15,"< """)+1)) Rick |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pick data from an array
WOW!!!
Thank you very much. This user group is phenomenal. "Rick Rothstein (MVP - VB)" wrote: Change the range and change 50 to number of cells in range e.g if range is 8 columns and 15 rows (120 cells in total ) then 50 becomes 120: =LARGE($A$1:$H$15,INT(RAND()*120+1)) And if you don't want to have to worry about the cell count, use this variation and just change both ranges each time you make a change in the number of cells included... =LARGE($A$1:$H$15,INT(RAND()*COUNTIF($A$1:$H$15,"< """)+1)) Rick |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract data value using pick list | Excel Worksheet Functions | |||
| How to pick data from different sheets conditionally? | Excel Worksheet Functions | |||
| Array Formula to Pick Average | Excel Discussion (Misc queries) | |||
| How I can pick a specific value on an array in excel | Excel Discussion (Misc queries) | |||
| Should Merging workbooks pick up new data or only edited data? | Excel Worksheet Functions | |||
Hybrid Mode
