Ranking values in multiple subsets using one 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 |
Ranking values in multiple subsets using one single formula
See other post
-- 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 02:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com