Random Number Generator

[InfoRN]

I have a list of providers with the following data:
lname,fname,mi,cat,specialty

I need to generate a random list of providers so that I can audit 25% of them.
AND ensure that each specialty is represented properly. (If 1/2 are ER docs then half of the 25% should be ER).

Suggestions?

Thanks,
Bob Shea, RN

 

[Russell Hauf]

On 2002-03-13 17:03, InfoRN wrote:
I have a list of providers with the following data:
lname,fname,mi,cat,specialty

I need to generate a random list of providers so that I can audit 25% of them.
AND ensure that each specialty is represented properly. (If 1/2 are ER docs then half of the 25% should be ER).

Suggestions?

Thanks,
Bob Shea, RN

Check the RAND and RANDBETWEEN functions in Help.

One thing: after you use either of these, I suggest that you copy the random numbers and then Paste Special - Values. Otherwise you get new random numbers each time the worksheet calculates.

Hope this helps,

Russell

 

[Mark W.]

You can use an Advanced AutoFilter to select your records based on a Criteria range. Suppose that you have the following data in cells A1:A21...

{"Specialty";"B";"B";"A";"D";"C";"A";"C";"B";"B";"D";"A";"A";"A";"A";"A";"A";"C";"B";"C";"B"}

There are 8 A's, 6 B's, and 4 C's in this data set. Let's say that you want to produce a 50% random sample of these Specialty codes that's proportional to their occurrence. First, setup a criteria range in C1:D4. C1:C4 should contain {"Specialty";"A";"B";"C"}. D2:D4 should contain the formula, =RAND()<=0.5. After selecting cells A1:A21 and choosing the Data | Filter | Advanced Filter... menu command, enter the Criteria Range, C1:D4, and press [ OK ].

The displayed records are a random sample that's proportional to the original occurrence of Specialty codes. Please note that this is a TRUE random sample -- like flipping a coin. We all know that with enought trials you'll get Heads 50% of the time. So with repeated trials of this sampling technique the average results will be 4 A's, 3 B's, and 2 C's.
This message was edited by Mark W. on 2002-03-14 09:36