Random sampling

[Kaylyn]

I have a worksheet that has column A (state) and column B (individual ID #). I need to review 100% of AZ, FL, IA, TX. But only a random 15% for every other state (i.e. 15% of HI, 15% of NY, 15% of TN, not an overall 15% if the remaining states). Can someone help me to write a formula for this?

 

[Rijnsent]

Hi Kaylyn,
how many occurances of each state do you have? And if you have e.g. 2 HI, is it okay that none (15% of 2 rounded down) are selected?
If so, you could e.g. do:
=IF(OR(B14="AZ",B14="FL"),"REVIEW",IF(RAND()<=0,15,"REVIEW","NO REVIEW"))
This formula gives AZ and FL a 100% check chance, and a line from any other state has a 15% chance of REVIEW.
Hope this helps,
Koen

 

[shg]

This will sample the required number of times:

	A​	B​	C​	D​	E​
1​	State​	Pct​	Num​	Check​
2​	AK	15%​	1​	OK​	C2: =CEILING(B2 * COUNTIF($A$9:$A$58,A2), 1)
3​	AL	15%​	2​	OK​	D3: =IF(COUNTIFS($A$9:$A$58, A3, $C$9:$C$58, "x") = C3, "OK", "oops")
4​	AR	15%​	2​	OK​
5​	AZ	100%​	19​	OK​
6​	CA	15%​	2​	OK​
7​
8​	State​	EIN​	Audit​
9​	AZ	24-3836833​	x​		C9: =IF(RAND() < (VLOOKUP(A9, $A$2:$C$6, 3) - COUNTIFS(A$8:A8, A9, C$8:C8, "x")) / COUNTIF(A9:A$58, A9), "x", "")
10​	AL	38-7301056​	​
11​	AR	23-0762875​	​
12​	AR	99-8190134​	​
13​	AZ	44-3676940​	x​
14​	AR	55-2551530​	x​
15​	AL	78-9182025​	​
16​	AL	56-4213949​	x​
17​	AZ	25-6162809​	x​
18​	AR	10-3487162​	​
19​	AR	52-3370061​	​
20​	AZ	69-0299513​	x​
21​	AL	95-0798353​	​
22​	AR	81-0659603​	​
23​	CA	66-3044196​	x​
24​	CA	11-6877591​	x​
25​	AK	18-7319437​	​
26​	AL	38-9253896​	​
27​	AZ	83-8356633​	x​
28​	AL	76-3343687​	x​
29​	CA	55-4004620​	​
30​	AL	14-9186665​	​
31​	AL	73-4323453​	​
32​	AK	71-4814791​	x​
33​	AL	81-7825259​	​
34​	AZ	17-8178045​	x​
35​	AZ	44-7218591​	x​
36​	AZ	72-6227715​	x​
37​	AZ	43-9338041​	x​
38​	AZ	96-4157370​	x​
39​	AZ	46-4727208​	x​
40​	CA	43-2886907​	​
41​	CA	22-0883195​	​
42​	AL	64-5727136​	​
43​	AZ	53-8839382​	x​
44​	AZ	69-9737976​	x​
45​	AK	48-4682310​	​
46​	AR	25-8637016​	​
47​	AZ	32-8042972​	x​
48​	CA	77-8854177​	​
49​	AR	52-3090127​	​
50​	AL	43-3024264​	​
51​	AZ	56-4864344​	x​
52​	CA	36-0689672​	​
53​	AZ	39-2147180​	x​
54​	AZ	54-5354252​	x​
55​	AR	32-2343370​	x​
56​	AK	82-4803409​	​
57​	AZ	40-3612562​	x​
58​	AZ	19-4173068​	x​

The state abbreviations in A2:A6 (extended as necessary) must be sorted.

 

[Kaylyn]

Thank you for the reply Rijn. There needs to one minimum for each state that appears on the sampling. So anything less than 2 would need to have 1 audit completed

 

[Kaylyn]

Thank you shg. I will give this a try!!