Random selection method

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.
 
T

Tyro

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
 
T

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.
 
J

joeu2004

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 ;->.)
 
Top