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)))))))