| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Ranking values in multiple subsets using one single formula | Excel Discussion (Misc queries) | |||
| Counting multiple values within single cells | Excel Worksheet Functions | |||
| Looking up multiple values for a single name | Excel Worksheet Functions | |||
| create a list of single values from multiple values | Excel Worksheet Functions | |||
| Search multiple values to return single values | Excel Worksheet Functions | |||
Linear Mode
