| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "brianalucas" <(E-Mail Removed)> wrote in message news  DD4D2BD-9EC4-4987-9ADE-(E-Mail Removed)...> 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(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. >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "T. Valko" <(E-Mail Removed)> wrote in message >> news:%(E-Mail Removed)... >> > 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. >> > >> > -- >> > Biff >> > Microsoft Excel MVP >> > >> > >> > "brianalucas" <(E-Mail Removed)> wrote in message >> > news:465B02FD-6A56-477A-908A-(E-Mail Removed)... >> >>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 >> >> >> > >> > >> >> >> |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| VLookup multiple values - sum returned values into single cell | se7098 | Microsoft Excel Worksheet Functions | 11 | 18th Sep 2008 12:04 AM |
| Ranking values in multiple subsets using one single formula | brianalucas | Microsoft Excel Misc | 1 | 14th Dec 2007 08:08 PM |
| Sum in vba only works for multiple values not for single values?? | Bevy | Microsoft Excel Programming | 0 | 15th Jun 2006 11:39 AM |
| create a list of single values from multiple values | =?Utf-8?B?Sm9yZGFu?= | Microsoft Excel Worksheet Functions | 3 | 3rd Nov 2005 11:25 PM |
| Search multiple values to return single values | =?Utf-8?B?SkFOQQ==?= | Microsoft Excel Worksheet Functions | 8 | 27th Oct 2005 04:26 PM |
