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:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

State​

[/td][td="bgcolor:#F3F3F3"]

Pct​

[/td][td="bgcolor:#F3F3F3"]

Num​

[/td][td="bgcolor:#F3F3F3"]

Check​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]AK[/td][td]

15%​

[/td][td="bgcolor:#FFFF99"]

1​

[/td][td="bgcolor:#CCFFFF"]

OK​

[/td][td="bgcolor:#FFFF99"]C2: =CEILING(B2 * COUNTIF($A$9:$A$58,A2), 1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]AL[/td][td]

15%​

[/td][td="bgcolor:#FFFF99"]

2​

[/td][td="bgcolor:#CCFFFF"]

OK​

[/td][td="bgcolor:#CCFFFF"]D3: =IF(COUNTIFS($A$9:$A$58, A3, $C$9:$C$58, "x") = C3, "OK", "oops")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]AR[/td][td]

15%​

[/td][td="bgcolor:#FFFF99"]

2​

[/td][td="bgcolor:#CCFFFF"]

OK​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]AZ[/td][td]

100%​

[/td][td="bgcolor:#FFFF99"]

19​

[/td][td="bgcolor:#CCFFFF"]

OK​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]CA[/td][td]

15%​

[/td][td="bgcolor:#FFFF99"]

2​

[/td][td="bgcolor:#CCFFFF"]

OK​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td="bgcolor:#F3F3F3"]

State​

[/td][td="bgcolor:#F3F3F3"]

EIN​

[/td][td="bgcolor:#F3F3F3"]

Audit​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]AZ[/td][td]

24-3836833​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td="bgcolor:#CCFFCC"]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", "")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]AL[/td][td]

38-7301056​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]AR[/td][td]

23-0762875​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]AR[/td][td]

99-8190134​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]AZ[/td][td]

44-3676940​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]AR[/td][td]

55-2551530​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]AL[/td][td]

78-9182025​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]AL[/td][td]

56-4213949​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]AZ[/td][td]

25-6162809​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]AR[/td][td]

10-3487162​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]AR[/td][td]

52-3370061​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]AZ[/td][td]

69-0299513​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]AL[/td][td]

95-0798353​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]AR[/td][td]

81-0659603​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]CA[/td][td]

66-3044196​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td]CA[/td][td]

11-6877591​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

25​

[/td][td]AK[/td][td]

18-7319437​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

26​

[/td][td]AL[/td][td]

38-9253896​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

27​

[/td][td]AZ[/td][td]

83-8356633​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

28​

[/td][td]AL[/td][td]

76-3343687​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

29​

[/td][td]CA[/td][td]

55-4004620​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

30​

[/td][td]AL[/td][td]

14-9186665​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

31​

[/td][td]AL[/td][td]

73-4323453​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

32​

[/td][td]AK[/td][td]

71-4814791​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

33​

[/td][td]AL[/td][td]

81-7825259​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

34​

[/td][td]AZ[/td][td]

17-8178045​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

35​

[/td][td]AZ[/td][td]

44-7218591​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

36​

[/td][td]AZ[/td][td]

72-6227715​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

37​

[/td][td]AZ[/td][td]

43-9338041​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

38​

[/td][td]AZ[/td][td]

96-4157370​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

39​

[/td][td]AZ[/td][td]

46-4727208​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

40​

[/td][td]CA[/td][td]

43-2886907​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

41​

[/td][td]CA[/td][td]

22-0883195​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

42​

[/td][td]AL[/td][td]

64-5727136​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

43​

[/td][td]AZ[/td][td]

53-8839382​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

44​

[/td][td]AZ[/td][td]

69-9737976​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

45​

[/td][td]AK[/td][td]

48-4682310​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

46​

[/td][td]AR[/td][td]

25-8637016​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

47​

[/td][td]AZ[/td][td]

32-8042972​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

48​

[/td][td]CA[/td][td]

77-8854177​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

49​

[/td][td]AR[/td][td]

52-3090127​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

50​

[/td][td]AL[/td][td]

43-3024264​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

51​

[/td][td]AZ[/td][td]

56-4864344​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

52​

[/td][td]CA[/td][td]

36-0689672​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

53​

[/td][td]AZ[/td][td]

39-2147180​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

54​

[/td][td]AZ[/td][td]

54-5354252​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

55​

[/td][td]AR[/td][td]

32-2343370​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

56​

[/td][td]AK[/td][td]

82-4803409​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

57​

[/td][td]AZ[/td][td]

40-3612562​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

58​

[/td][td]AZ[/td][td]

19-4173068​

[/td][td="bgcolor:#CCFFCC"]

x​

[/td][td][/td][td][/td][/tr]
[/table]

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!!