How can I rank values in multiple subsets using a single formula?

[brianalucas]

I am attempting to select a random sample population from the given set of
values in column B. Of the values in column B, only a specific quantity, as
specified in cells E2, F2, and G2 will be used. In column C I use the
=rand() formula. Then in column D I use the formula
=Rank(D2,$D$2:$D$6)<=$E$2, =Rank(D7,$D$7:$D$12=$F$2,
=Rank(D13,$D$13:$D$16)<=$G$2. A value of "TRUE" returned in column D means
that only those values are my random sample.

Obviously, the shortcoming of this system is that I have to write more than
one formula in column D, each time manually looking to see where the A subset
values end, where the B subset values end, etc..

Here's my question: How can I combine the above 3 formulas into one single
formula?

A B C D E
F G
1 Subset Values =Rand() Sample Pop. A B C
2 A 63 .2343 FALSE 2 3 2
3 A 88 .3433 FALSE
4 A 56 .6522 TRUE
5 A 45 .4355 FALSE
6 A 94 .8622 TRUE
7 B 48 .3545 FALSE
8 B 69 .6251 TRUE
9 B 53 .1245 FALSE
10 B 62 .7532 TRUE
11 B 71 .9811 TRUE
12 B 79 .2722 FALSE
13 C 83 .1452 FALSE
14 C 92 .5864 TRUE
15 C 50 .4291 FALSE
16 C 75 .6291 TRUE

 

[T. Valko]

It turns into a monster formula!

=IF(AND(A2="A",SUMPRODUCT(--(A$2:A$16="A"),--(C2<C$2:C$16))+1<=E$2),"A",IF(AND(A2="B",SUMPRODUCT(--(A$2:A$16="B"),--(C2<C$2:C$16))+1=F$2),"B",IF(AND(A2="C",SUMPRODUCT(--(A$2:A$16="C"),--(C2<C$2:C$16))+1<=G$2),"C","")))

Copy down as needed.

 

[T. Valko]

Improvement (in efficiency):

=IF(A2="A",IF(SUMPRODUCT(--(A$2:A$16="A"),--(C2<C$2:C$16))+1<=E$2,"A",""),IF(A2="B",IF(SUMPRODUCT(--(A$2:A$16="B"),--(C2<C$2:C$16))+1=F$2,"B",""),IF(A2="C",IF(SUMPRODUCT(--(A$2:A$16="C"),--(C2<C$2:C$16))+1<=G$2,"C",""),"")))

This version calculates about 1.5 times faster.

 

[brianalucas]

Biff.... you are a genius. Thank you for taking the time to figure that out.
Much appreciated.

Brian

 

[T. Valko]

You're welcome. Thanks for the feedback!