ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   How can I rank values in multiple subsets using a single formula? (https://www.excelbanter.com/excel-discussion-misc-queries/169735-how-can-i-rank-values-multiple-subsets-using-single-formula.html)

brianalucas

How can I rank values in multiple subsets using a single formula?
 
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

How can I rank values in multiple subsets using a single formula?
 
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",SUMPRODUC T(--(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.

--
Biff
Microsoft Excel MVP


"brianalucas" wrote in message
...
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

How can I rank values in multiple subsets using a single formula?
 
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(SUMPROD UCT(--(A$2:A$16="B"),--(C2<C$2:C$16))+1=F$2,"B",""),IF(A2="C",IF(SUMPRODU CT(--(A$2:A$16="C"),--(C2<C$2:C$16))+1<=G$2,"C",""),"")))

This version calculates about 1.5 times faster.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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",SUMPRODUC T(--(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.

--
Biff
Microsoft Excel MVP


"brianalucas" wrote in message
...
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






brianalucas

How can I rank values in multiple subsets using a single formu
 
Biff.... you are a genius. Thank you for taking the time to figure that out.
Much appreciated.

Brian


"T. Valko" wrote:

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(SUMPROD UCT(--(A$2:A$16="B"),--(C2<C$2:C$16))+1=F$2,"B",""),IF(A2="C",IF(SUMPRODU CT(--(A$2:A$16="C"),--(C2<C$2:C$16))+1<=G$2,"C",""),"")))

This version calculates about 1.5 times faster.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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",SUMPRODUC T(--(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.

--
Biff
Microsoft Excel MVP


"brianalucas" wrote in message
...
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

How can I rank values in multiple subsets using a single formu
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"brianalucas" wrote in message
...
Biff.... you are a genius. Thank you for taking the time to figure that
out.
Much appreciated.

Brian


"T. Valko" wrote:

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(SUMPROD UCT(--(A$2:A$16="B"),--(C2<C$2:C$16))+1=F$2,"B",""),IF(A2="C",IF(SUMPRODU CT(--(A$2:A$16="C"),--(C2<C$2:C$16))+1<=G$2,"C",""),"")))

This version calculates about 1.5 times faster.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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",SUMPRODUC T(--(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.

--
Biff
Microsoft Excel MVP


"brianalucas" wrote in message
...
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










All times are GMT +1. The time now is 04:59 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com