Random selection method

  • Thread starter Thread starter Renegade
  • Start date Start date
R

Renegade

In an Excel spreadsheet, is there a method to randomly select criteria off
the worksheet? For example, I need to randomly select (1% or 40 items) from
the list to conduct an analysis. If this can be accomplished, would you be
willing to share your process for accomplishing same. Thanks in advance.
 
One way. If your numbers are in A1:A100 then in B1 put
=IF(RAND()<=0.1,"Select this","")
Change the fraction 0.1 until you're satisfied with the selection.. Then
copy the cells B1:B100 and
paste special/values back into B1:B100 or another column because RAND() is
a volatile function
and is recalculated anytime anything else on the worksheet is calculated
RAND() generates random numbers from 0 through less than 1 - i.e.
0.99999999999999999 or so..

Tyro
 
Correction:
If your numbers are in A1:A100 then in B1 put =IF(RAND()<=0.1,"Select
this","")
should read:
If your numbers are in A1:A100 then in B1 put =IF(RAND()<=0.1,"Select
this","") and drag down through B100.
 
Renegade said:
In an Excel spreadsheet, is there a method to randomly select criteria off
the worksheet? For example, I need to randomly select (1% or 40 items)
from the list to conduct an analysis.
Click to expand...

Not sure what you mean by "select criteria off the worksheet". I assume you
mean that you need to select 1% (40 items) from a list presumably of 4000.
If I misunderstood and you do not get a better answer, please post back with
an example. Following my assumption....

Suppose your data is in A1:A4000. In a column of 4000 cells, say Z1:Z4000,
put the formula =RAND() in each cell. Then in a column (or row) of 40 cells,
say B1:B40, put the following formula, starting in B1 and copying down:

=index($A$1:$A$4000, rank(Z1, $Z$1:$Z$4000))

(Based on a solution by RagDyer for a different problem.)

Notes:

1. Note that the only relevative reference is the first argument of the RANK
function.

2. It would be prudent to cut and Paste Special>Value the range containing
the RAND() formula. Otherwise, it will change every time you modify the
worksheet. You can over-paste the original range. Alternatively, Paste
Special>Value into another range, refer to that range in the RANK function,
letting the RAND range change without effect. That provides you with a ready
set of new random numbers anytime you want to change the subset.

3. Although all the ranges in my example are parallel, that is not a
requirement for this method. That is, the ranges can start anywhere and go
in any direction (column or row) that they will fit. (Good luck finding 4000
columns ;->.)
 
Back
Top