Random Number Generator

InfoRN · Mar 13th, 2002, 06:03 PM

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 · Mar 13th, 2002, 06:40 PM

Quote:

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. · Mar 14th, 2002, 10:32 AM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 13th, 2002, 06:03 PM	#1
InfoRN New Member Join Date: Mar 2002 Posts: 1	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

Mar 14th, 2002, 10:32 AM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]