Pull a Sample from a Population

[Guest]

I need a statistically valid, randomly selected breakout of transactions from
a total population of all transactions. Would like to have Excel generate a
statistically valid list from a total population of about 38K transactions.
All 38K transactions, regardless of dollar value, are subject to review
through random selection based on the n of total population. The list should
also incude any transaction >= $5,000. The listing should include all data
elements of the select transactions.

Possible? How?

 

[Bernie Deitrick]

Dazed,

Insert a column of formulas, like this (assumes that the amount is in column D, for example)

=OR(D2>=5000,(Rand()*100)>99)

Change the 99 to a lower number to choose a greater # of items randomly... Then filter your
database based on the column of formulas, and choose to show TRUE. The transactions still visible
are those that should be reviewed.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Got it! Thanks! Jon

Dazed,

Insert a column of formulas, like this (assumes that the amount is in column D, for example)

=OR(D2>=5000,(Rand()*100)>99)

Change the 99 to a lower number to choose a greater # of items randomly... Then filter your
database based on the column of formulas, and choose to show TRUE. The transactions still visible
are those that should be reviewed.

HTH,
Bernie
MS Excel MVP

 

[squenson via OfficeKB.com]

I would create a new column with the formula in row 2: =IF(D2>5000,0,RAND())
(assuming that D is the column with the transaction amounts), then copy it
down till the row 38,000. Then I would select the entire column, press Ctrl-C,
then right-click, select Paste Special and click on Value. This way, this new
column has a set of random numbers (or 0's) and it is frozen.

Then, I would use AutoFilter to filter on this column, use Custom and select
all the values less than 0.05 if I want 5% of the transactions, or less than
0.23 if I want 23% of the transactions. As transactions above $5,000 have a
value 0, they are always selected. Then, you can copy the filtered range
somewhere else and perform the review of the transactions.