Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex if
I may be pushing my luck here, but here it goes.....
I have 3 IF then array statements. They all seem to work, though I don't understand the output. I am trying to get the average. 1. The first result is an overall average. 2. The second one should be the same but excludes "chevy" 3. The third one should be the same but includes "chevy" only. I am not sure why the first average is the lowest value. Doesn't make sense. The formulas (array) a 1. =AVERAGE(IF('Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) 2. =AVERAGE(IF('Enroll I'!$C$2:$C$2921<"Chevy",'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) 3. =AVERAGE(IF('Enroll I'!$C$2:$C$2921="Chevy",'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) To make them 1 more similar to #2 and 3., I tried....which doesn't work at all. =AVERAGE(IF(OR('Enroll I'!$C$2:$C$2921<"Chevy",'Enroll I'!$C$2:$C$2921="Chevy"),'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex if
"PAL" wrote:
To make them 1 more similar to #2 and 3., I tried.... which doesn't work at all. Because I believe formulas #2 and 3 were wrong from the start. You should start with #1 and recreate #2 and 3. Also note: formula #1 can and should be simplified to minimize nested formulas, especially if you are using a pre-2007 version of Excel. I am trying to get the average. 1. The first result is an overall average. 2. The second one should be the same but excludes "chevy" 3. The third one should be the same but includes "chevy" only. Try the following (NOTE: be sure to copy-and-paste in order to avoid typos -- except mine :( ): Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Does that work for you? ----- original message ----- "PAL" wrote in message ... I may be pushing my luck here, but here it goes..... I have 3 IF then array statements. They all seem to work, though I don't understand the output. I am trying to get the average. 1. The first result is an overall average. 2. The second one should be the same but excludes "chevy" 3. The third one should be the same but includes "chevy" only. I am not sure why the first average is the lowest value. Doesn't make sense. The formulas (array) a 1. =AVERAGE(IF('Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) 2. =AVERAGE(IF('Enroll I'!$C$2:$C$2921<"Chevy",'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) 3. =AVERAGE(IF('Enroll I'!$C$2:$C$2921="Chevy",'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) To make them 1 more similar to #2 and 3., I tried....which doesn't work at all. =AVERAGE(IF(OR('Enroll I'!$C$2:$C$2921<"Chevy",'Enroll I'!$C$2:$C$2921="Chevy"),'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex if
Clarification....
Sigh, it appears that Outlook Express misinterpreted my spacing, leading to some mangled lines. If that's a problem for you, perhaps the following will work better (crossing my fingers that OE does not misinterpret me again): Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) ----- original message ----- "JoeU2004" wrote in message ... "PAL" wrote: To make them 1 more similar to #2 and 3., I tried.... which doesn't work at all. Because I believe formulas #2 and 3 were wrong from the start. You should start with #1 and recreate #2 and 3. Also note: formula #1 can and should be simplified to minimize nested formulas, especially if you are using a pre-2007 version of Excel. I am trying to get the average. 1. The first result is an overall average. 2. The second one should be the same but excludes "chevy" 3. The third one should be the same but includes "chevy" only. Try the following (NOTE: be sure to copy-and-paste in order to avoid typos -- except mine :( ): Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Does that work for you? ----- original message ----- "PAL" wrote in message ... I may be pushing my luck here, but here it goes..... I have 3 IF then array statements. They all seem to work, though I don't understand the output. I am trying to get the average. 1. The first result is an overall average. 2. The second one should be the same but excludes "chevy" 3. The third one should be the same but includes "chevy" only. I am not sure why the first average is the lowest value. Doesn't make sense. The formulas (array) a 1. =AVERAGE(IF('Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) 2. =AVERAGE(IF('Enroll I'!$C$2:$C$2921<"Chevy",'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) 3. =AVERAGE(IF('Enroll I'!$C$2:$C$2921="Chevy",'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) To make them 1 more similar to #2 and 3., I tried....which doesn't work at all. =AVERAGE(IF(OR('Enroll I'!$C$2:$C$2921<"Chevy",'Enroll I'!$C$2:$C$2921="Chevy"),'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex if
Errata....
Sorry for the incessant repostings, but I just noticed a typo that is sure to screw you up -- a missing parenthesis in each formula. Try: Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921)) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921)) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921)) ----- original message ----- "JoeU2004" wrote in message ... Clarification.... Sigh, it appears that Outlook Express misinterpreted my spacing, leading to some mangled lines. If that's a problem for you, perhaps the following will work better (crossing my fingers that OE does not misinterpret me again): Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) ----- original message ----- "JoeU2004" wrote in message ... "PAL" wrote: To make them 1 more similar to #2 and 3., I tried.... which doesn't work at all. Because I believe formulas #2 and 3 were wrong from the start. You should start with #1 and recreate #2 and 3. Also note: formula #1 can and should be simplified to minimize nested formulas, especially if you are using a pre-2007 version of Excel. I am trying to get the average. 1. The first result is an overall average. 2. The second one should be the same but excludes "chevy" 3. The third one should be the same but includes "chevy" only. Try the following (NOTE: be sure to copy-and-paste in order to avoid typos -- except mine :( ): Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Does that work for you? ----- original message ----- "PAL" wrote in message ... I may be pushing my luck here, but here it goes..... I have 3 IF then array statements. They all seem to work, though I don't understand the output. I am trying to get the average. 1. The first result is an overall average. 2. The second one should be the same but excludes "chevy" 3. The third one should be the same but includes "chevy" only. I am not sure why the first average is the lowest value. Doesn't make sense. The formulas (array) a 1. =AVERAGE(IF('Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) 2. =AVERAGE(IF('Enroll I'!$C$2:$C$2921<"Chevy",'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) 3. =AVERAGE(IF('Enroll I'!$C$2:$C$2921="Chevy",'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) To make them 1 more similar to #2 and 3., I tried....which doesn't work at all. =AVERAGE(IF(OR('Enroll I'!$C$2:$C$2921<"Chevy",'Enroll I'!$C$2:$C$2921="Chevy"),'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex if
Caveat emptor....
I wrote: Also note: formula #1 can and should be simplified to minimize nested formulas, especially if you are using a pre-2007 version of Excel. One reason to nest IF() expressions instead of using AND() is if some cells in, say, L2:L2921 are blank when H2:H2921L5 or H2:H2921N5 is true. In that case, the condition L2:L29210 would return false positives (TRUE). Even if that is the case, the original formulas can still benefit from some simplification. If you depend on side-effects like that, the following is a safe alternative. PS: I have assumed all along that you know to enter these as array formulas; i.e. press ctrl+shift+Enter instead of simply Enter. Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF('Enroll I'!$H$2:$H$2921=$L$5, IF('Enroll I'!$H$2:$H$2921<=$N$5, IF(AND('Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes"), IF('Enroll I'!$K$2:$K$29210, 'Enroll I'!$L$2:$L$2921))))) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5), IF('Enroll I'!$H$2:$H$2921<=$N$5, IF(AND('Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes"), IF('Enroll I'!$K$2:$K$29210, 'Enroll I'!$L$2:$L$2921))))) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5), IF('Enroll I'!$H$2:$H$2921<=$N$5, IF(AND('Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes"), IF('Enroll I'!$K$2:$K$29210, 'Enroll I'!$L$2:$L$2921))))) ----- original message ----- "JoeU2004" wrote in message ... Errata.... Sorry for the incessant repostings, but I just noticed a typo that is sure to screw you up -- a missing parenthesis in each formula. Try: Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921)) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921)) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921)) ----- original message ----- "JoeU2004" wrote in message ... Clarification.... Sigh, it appears that Outlook Express misinterpreted my spacing, leading to some mangled lines. If that's a problem for you, perhaps the following will work better (crossing my fingers that OE does not misinterpret me again): Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) ----- original message ----- "JoeU2004" wrote in message ... "PAL" wrote: To make them 1 more similar to #2 and 3., I tried.... which doesn't work at all. Because I believe formulas #2 and 3 were wrong from the start. You should start with #1 and recreate #2 and 3. Also note: formula #1 can and should be simplified to minimize nested formulas, especially if you are using a pre-2007 version of Excel. I am trying to get the average. 1. The first result is an overall average. 2. The second one should be the same but excludes "chevy" 3. The third one should be the same but includes "chevy" only. Try the following (NOTE: be sure to copy-and-paste in order to avoid typos -- except mine :( ): Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Does that work for you? ----- original message ----- "PAL" wrote in message ... I may be pushing my luck here, but here it goes..... I have 3 IF then array statements. They all seem to work, though I don't understand the output. I am trying to get the average. 1. The first result is an overall average. 2. The second one should be the same but excludes "chevy" 3. The third one should be the same but includes "chevy" only. I am not sure why the first average is the lowest value. Doesn't make sense. The formulas (array) a 1. =AVERAGE(IF('Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) 2. =AVERAGE(IF('Enroll I'!$C$2:$C$2921<"Chevy",'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) 3. =AVERAGE(IF('Enroll I'!$C$2:$C$2921="Chevy",'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) To make them 1 more similar to #2 and 3., I tried....which doesn't work at all. =AVERAGE(IF(OR('Enroll I'!$C$2:$C$2921<"Chevy",'Enroll I'!$C$2:$C$2921="Chevy"),'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex if
I will have to use this way as there are blanks. However, when I run this
formula, at least #1, I get the DIV/0 error. "JoeU2004" wrote: Caveat emptor.... I wrote: Also note: formula #1 can and should be simplified to minimize nested formulas, especially if you are using a pre-2007 version of Excel. One reason to nest IF() expressions instead of using AND() is if some cells in, say, L2:L2921 are blank when H2:H2921L5 or H2:H2921N5 is true. In that case, the condition L2:L29210 would return false positives (TRUE). Even if that is the case, the original formulas can still benefit from some simplification. If you depend on side-effects like that, the following is a safe alternative. PS: I have assumed all along that you know to enter these as array formulas; i.e. press ctrl+shift+Enter instead of simply Enter. Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF('Enroll I'!$H$2:$H$2921=$L$5, IF('Enroll I'!$H$2:$H$2921<=$N$5, IF(AND('Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes"), IF('Enroll I'!$K$2:$K$29210, 'Enroll I'!$L$2:$L$2921))))) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5), IF('Enroll I'!$H$2:$H$2921<=$N$5, IF(AND('Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes"), IF('Enroll I'!$K$2:$K$29210, 'Enroll I'!$L$2:$L$2921))))) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5), IF('Enroll I'!$H$2:$H$2921<=$N$5, IF(AND('Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes"), IF('Enroll I'!$K$2:$K$29210, 'Enroll I'!$L$2:$L$2921))))) ----- original message ----- "JoeU2004" wrote in message ... Errata.... Sorry for the incessant repostings, but I just noticed a typo that is sure to screw you up -- a missing parenthesis in each formula. Try: Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921)) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921)) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921)) ----- original message ----- "JoeU2004" wrote in message ... Clarification.... Sigh, it appears that Outlook Express misinterpreted my spacing, leading to some mangled lines. If that's a problem for you, perhaps the following will work better (crossing my fingers that OE does not misinterpret me again): Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) ----- original message ----- "JoeU2004" wrote in message ... "PAL" wrote: To make them 1 more similar to #2 and 3., I tried.... which doesn't work at all. Because I believe formulas #2 and 3 were wrong from the start. You should start with #1 and recreate #2 and 3. Also note: formula #1 can and should be simplified to minimize nested formulas, especially if you are using a pre-2007 version of Excel. I am trying to get the average. 1. The first result is an overall average. 2. The second one should be the same but excludes "chevy" 3. The third one should be the same but includes "chevy" only. Try the following (NOTE: be sure to copy-and-paste in order to avoid typos -- except mine :( ): Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Does that work for you? ----- original message ----- "PAL" wrote in message ... I may be pushing my luck here, but here it goes..... I have 3 IF then array statements. They all seem to work, though I don't understand the output. I am trying to get the average. 1. The first result is an overall average. 2. The second one should be the same but excludes "chevy" 3. The third one should be the same but includes "chevy" only. I am not sure why the first average is the lowest value. Doesn't make sense. The formulas (array) a 1. =AVERAGE(IF('Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) 2. =AVERAGE(IF('Enroll I'!$C$2:$C$2921<"Chevy",'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) 3. =AVERAGE(IF('Enroll I'!$C$2:$C$2921="Chevy",'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) To make them 1 more similar to #2 and 3., I tried....which doesn't work at all. =AVERAGE(IF(OR('Enroll I'!$C$2:$C$2921<"Chevy",'Enroll I'!$C$2:$C$2921="Chevy"),'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex if
#2 and 3 return 0's
"JoeU2004" wrote: Caveat emptor.... I wrote: Also note: formula #1 can and should be simplified to minimize nested formulas, especially if you are using a pre-2007 version of Excel. One reason to nest IF() expressions instead of using AND() is if some cells in, say, L2:L2921 are blank when H2:H2921L5 or H2:H2921N5 is true. In that case, the condition L2:L29210 would return false positives (TRUE). Even if that is the case, the original formulas can still benefit from some simplification. If you depend on side-effects like that, the following is a safe alternative. PS: I have assumed all along that you know to enter these as array formulas; i.e. press ctrl+shift+Enter instead of simply Enter. Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF('Enroll I'!$H$2:$H$2921=$L$5, IF('Enroll I'!$H$2:$H$2921<=$N$5, IF(AND('Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes"), IF('Enroll I'!$K$2:$K$29210, 'Enroll I'!$L$2:$L$2921))))) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5), IF('Enroll I'!$H$2:$H$2921<=$N$5, IF(AND('Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes"), IF('Enroll I'!$K$2:$K$29210, 'Enroll I'!$L$2:$L$2921))))) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5), IF('Enroll I'!$H$2:$H$2921<=$N$5, IF(AND('Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes"), IF('Enroll I'!$K$2:$K$29210, 'Enroll I'!$L$2:$L$2921))))) ----- original message ----- "JoeU2004" wrote in message ... Errata.... Sorry for the incessant repostings, but I just noticed a typo that is sure to screw you up -- a missing parenthesis in each formula. Try: Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921)) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921)) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921)) ----- original message ----- "JoeU2004" wrote in message ... Clarification.... Sigh, it appears that Outlook Express misinterpreted my spacing, leading to some mangled lines. If that's a problem for you, perhaps the following will work better (crossing my fingers that OE does not misinterpret me again): Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) ----- original message ----- "JoeU2004" wrote in message ... "PAL" wrote: To make them 1 more similar to #2 and 3., I tried.... which doesn't work at all. Because I believe formulas #2 and 3 were wrong from the start. You should start with #1 and recreate #2 and 3. Also note: formula #1 can and should be simplified to minimize nested formulas, especially if you are using a pre-2007 version of Excel. I am trying to get the average. 1. The first result is an overall average. 2. The second one should be the same but excludes "chevy" 3. The third one should be the same but includes "chevy" only. Try the following (NOTE: be sure to copy-and-paste in order to avoid typos -- except mine :( ): Formula #1 (intended to be functionally equivalent to what you had): =AVERAGE(IF(AND('Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #2 (same as #1 with the additional constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Formula #3 (same as #2 with the complementary constraint): =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy", 'Enroll I'!$H$2:$H$2921=$L$5, 'Enroll I'!$H$2:$H$2921<=$N$5, 'Enroll I'!$L$2:$L$29210, 'Enroll I'!$P$2:$P$2921="Treatment", 'Enroll I'!$M$2:$M$2921="Yes", 'Enroll I'!$K$2:$K$29210), 'Enroll I'!$L$2:$L$2921) Does that work for you? ----- original message ----- "PAL" wrote in message ... I may be pushing my luck here, but here it goes..... I have 3 IF then array statements. They all seem to work, though I don't understand the output. I am trying to get the average. 1. The first result is an overall average. 2. The second one should be the same but excludes "chevy" 3. The third one should be the same but includes "chevy" only. I am not sure why the first average is the lowest value. Doesn't make sense. The formulas (array) a 1. =AVERAGE(IF('Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) 2. =AVERAGE(IF('Enroll I'!$C$2:$C$2921<"Chevy",'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) 3. =AVERAGE(IF('Enroll I'!$C$2:$C$2921="Chevy",'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) To make them 1 more similar to #2 and 3., I tried....which doesn't work at all. =AVERAGE(IF(OR('Enroll I'!$C$2:$C$2921<"Chevy",'Enroll I'!$C$2:$C$2921="Chevy"),'Enroll I'!$H$2:$H$2921=$L$5,IF('Enroll I'!$H$2:$H$2921<=$N$5,IF('Enroll I'!$L$2:$L$29210,IF('Enroll I'!$P$2:$P$2921="Treatment",IF('Enroll I'!$M$2:$M$2921="Yes",IF('Enroll I'!$K$2:$K$29210,'Enroll I'!$L$2:$L$2921))))))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex IF | Excel Discussion (Misc queries) | |||
Bit of a complex SUM | Excel Worksheet Functions | |||
Complex IF | Excel Worksheet Functions | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
Complex sum | Excel Discussion (Misc queries) |