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

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

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

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

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

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

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

"PAL" wrote:
when I run this formula, at least #1, I get the DIV/0 error.

That would suggest that none of the cells in the range L2:L2921 meets all of
the conditions for including them in the average.


But later you write:
#2 and 3 return 0's

That would suggest that all of the cells in the range L2:L2921 that meet all
of the conditions are either empty, have text, or have the numerical value
zero.

But that contradicts your first statement, since the formulas in #2 and #3
simply add yet-another condition. If #1 (the entire set) is null, #2 and #3
(subsets) cannot be non-null.

So I suspect there is something wrong with the information you are providing
and/or with the formulas as you copied them into your worksheet.

Did you copy-and-paste the formulas (my 4th attempt!) from your news reader
into Excel, as I suggested?


That does not rule the possibility that there is __also__ something wrong
with the formulas that I provided, in the first place.

However, I just compared my #1 to your original #1, and I see no functional
difference.

You can do the same thing. Copy-and-paste the formulas into Notepad, then
break them apart so that each line starts with "IF". I will copy-and-paste
my results below. I just hope they are not get mangled by OE. (They
shouldn't be; the lines are short. But....)

Your original #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)))))))

My version of #1:

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

The only difference that I see is the optimization of combining the 3rd, 4th
and 5th conditions into an AND() expression. That should be functionally
equivalent.

As for my #2 and #3, I simply added the subset condition to your original
1st condition. In other words:

#1: =AVERAGE(IF('Enroll I'!$H$2:$H$2921=$L$5,

#2: =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy",
'Enroll I'!$H$2:$H$2921=$L$5),

#3: =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy",
'Enroll I'!$H$2:$H$2921=$L$5),

(I wish I could add indentation to improve readability; but I no longer
trust OE to do the right thing.)

The rest of #2 and #3 were copy-and-pasted from #1; that is, they are carbon
copies.

So I think it should be readily apparent that #2 and #3 do what you asked
for, namely: average subsets from the entire qualifying range that meets
the additional condition (<"Chevy" or ="Chevy").

In summary, if there is an error in my #1, #2 or #3, I suspect it was in the
original formulas that you posted, since this version of my #1, #2 and #3 is
simply copy-and-pasted from your original formulas and modified in a
non-material way, barring typos.

(I keep looking for typos. I just don't see any, at least not in my 4th
attempt to get it right! <sigh)

Sorry, but I think that's as far as I can take this in this forum. I think
the data dependencies of the formula is too complicated to debug here, even
if you provided additional details.

If you would like, send me the workbook with some explanation of where to
find the problematic formulas. I might be able to spot any obvious flaws
(or questions) in your logic or in the process of copying my formulas.

If you want to try that, send the workbook and explanation to joeu2004 "at"
hotmail.com.


----- original message -----

"PAL" wrote in message
...
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)))))))

Minor errata....

I wrote:
#2 and 3 return 0's

That would suggest that all of the cells in the range L2:L2921 that meet
all of the conditions are either empty, have text, or have the numerical
value zero.

Actually, it suggests that those cells that meet the conditions contain
zero -- assuming that #2 and #3 return exactly zero.

But the rest that I wrote is still valid. The point is: the #DIV/0 error
returned by #1 suggests that no cells meet the conditions, but the non-error
result of #2 and #3 suggests that some cells do meet those conditions (along
with the additional condition). That is a contradiction.


----- original message -----

"JoeU2004" wrote in message
...
"PAL" wrote:
when I run this formula, at least #1, I get the DIV/0 error.

That would suggest that none of the cells in the range L2:L2921 meets all
of the conditions for including them in the average.


But later you write:
#2 and 3 return 0's

That would suggest that all of the cells in the range L2:L2921 that meet
all of the conditions are either empty, have text, or have the numerical
value zero.

But that contradicts your first statement, since the formulas in #2 and #3
simply add yet-another condition. If #1 (the entire set) is null, #2 and
#3 (subsets) cannot be non-null.

So I suspect there is something wrong with the information you are
providing and/or with the formulas as you copied them into your worksheet.

Did you copy-and-paste the formulas (my 4th attempt!) from your news
reader into Excel, as I suggested?


That does not rule the possibility that there is __also__ something wrong
with the formulas that I provided, in the first place.

However, I just compared my #1 to your original #1, and I see no
functional difference.

You can do the same thing. Copy-and-paste the formulas into Notepad, then
break them apart so that each line starts with "IF". I will
copy-and-paste my results below. I just hope they are not get mangled by
OE. (They shouldn't be; the lines are short. But....)

Your original #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)))))))

My version of #1:

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

The only difference that I see is the optimization of combining the 3rd,
4th and 5th conditions into an AND() expression. That should be
functionally equivalent.

As for my #2 and #3, I simply added the subset condition to your original
1st condition. In other words:

#1: =AVERAGE(IF('Enroll I'!$H$2:$H$2921=$L$5,

#2: =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy",
'Enroll I'!$H$2:$H$2921=$L$5),

#3: =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy",
'Enroll I'!$H$2:$H$2921=$L$5),

(I wish I could add indentation to improve readability; but I no longer
trust OE to do the right thing.)

The rest of #2 and #3 were copy-and-pasted from #1; that is, they are
carbon copies.

So I think it should be readily apparent that #2 and #3 do what you asked
for, namely: average subsets from the entire qualifying range that meets
the additional condition (<"Chevy" or ="Chevy").

In summary, if there is an error in my #1, #2 or #3, I suspect it was in
the original formulas that you posted, since this version of my #1, #2 and
#3 is simply copy-and-pasted from your original formulas and modified in a
non-material way, barring typos.

(I keep looking for typos. I just don't see any, at least not in my 4th
attempt to get it right! <sigh)

Sorry, but I think that's as far as I can take this in this forum. I
think the data dependencies of the formula is too complicated to debug
here, even if you provided additional details.

If you would like, send me the workbook with some explanation of where to
find the problematic formulas. I might be able to spot any obvious flaws
(or questions) in your logic or in the process of copying my formulas.

If you want to try that, send the workbook and explanation to joeu2004
"at" hotmail.com.


----- original message -----

"PAL" wrote in message
...
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)))))))

Minor errata #2....

Argh! Let me try this again.


I wrote:
when I run this formula, at least #1, I get the DIV/0 error.

That would suggest that none of the cells in the range L2:L2921
meets all of the conditions for including them in the average.

Actually, it means either that none of cells meets all of the condition, or
that the cells that do meet all of the conditions have contents that ignored
by AVERAGE (e.g. empty or text).


#2 and 3 return 0's

That would suggest that all of the cells in the range L2:L2921 that meet
all of the conditions are either empty, have text, or have the numerical
value zero.

Actually, it means that some cells that meet all conditions (along with the
additional condition) have numerical values, and the average of those cells
is zero.

The rest of what I wrote is still valid. The point is: the #DIV/0 error
returned by #1 suggests that no cells meet the conditions, but the non-error
result of #2 and #3 suggests that some cells do meet those conditions (along
with the additional condition). That is a contradiction.


----- original message -----

"JoeU2004" wrote in message
...
Minor errata....

I wrote:
#2 and 3 return 0's

That would suggest that all of the cells in the range L2:L2921 that meet
all of the conditions are either empty, have text, or have the numerical
value zero.

Actually, it suggests that those cells that meet the conditions contain
zero -- assuming that #2 and #3 return exactly zero.

But the rest that I wrote is still valid. The point is: the #DIV/0 error
returned by #1 suggests that no cells meet the conditions, but the
non-error result of #2 and #3 suggests that some cells do meet those
conditions (along with the additional condition). That is a
contradiction.


----- original message -----

"JoeU2004" wrote in message
...
"PAL" wrote:
when I run this formula, at least #1, I get the DIV/0 error.

That would suggest that none of the cells in the range L2:L2921 meets all
of the conditions for including them in the average.


But later you write:
#2 and 3 return 0's

That would suggest that all of the cells in the range L2:L2921 that meet
all of the conditions are either empty, have text, or have the numerical
value zero.

But that contradicts your first statement, since the formulas in #2 and
#3 simply add yet-another condition. If #1 (the entire set) is null, #2
and #3 (subsets) cannot be non-null.

So I suspect there is something wrong with the information you are
providing and/or with the formulas as you copied them into your
worksheet.

Did you copy-and-paste the formulas (my 4th attempt!) from your news
reader into Excel, as I suggested?


That does not rule the possibility that there is __also__ something wrong
with the formulas that I provided, in the first place.

However, I just compared my #1 to your original #1, and I see no
functional difference.

You can do the same thing. Copy-and-paste the formulas into Notepad,
then break them apart so that each line starts with "IF". I will
copy-and-paste my results below. I just hope they are not get mangled by
OE. (They shouldn't be; the lines are short. But....)

Your original #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)))))))

My version of #1:

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

The only difference that I see is the optimization of combining the 3rd,
4th and 5th conditions into an AND() expression. That should be
functionally equivalent.

As for my #2 and #3, I simply added the subset condition to your original
1st condition. In other words:

#1: =AVERAGE(IF('Enroll I'!$H$2:$H$2921=$L$5,

#2: =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921<"Chevy",
'Enroll I'!$H$2:$H$2921=$L$5),

#3: =AVERAGE(IF(AND('Enroll I'!$C$2:$C$2921="Chevy",
'Enroll I'!$H$2:$H$2921=$L$5),

(I wish I could add indentation to improve readability; but I no longer
trust OE to do the right thing.)

The rest of #2 and #3 were copy-and-pasted from #1; that is, they are
carbon copies.

So I think it should be readily apparent that #2 and #3 do what you asked
for, namely: average subsets from the entire qualifying range that meets
the additional condition (<"Chevy" or ="Chevy").

In summary, if there is an error in my #1, #2 or #3, I suspect it was in
the original formulas that you posted, since this version of my #1, #2
and #3 is simply copy-and-pasted from your original formulas and modified
in a non-material way, barring typos.

(I keep looking for typos. I just don't see any, at least not in my 4th
attempt to get it right! <sigh)

Sorry, but I think that's as far as I can take this in this forum. I
think the data dependencies of the formula is too complicated to debug
here, even if you provided additional details.

If you would like, send me the workbook with some explanation of where to
find the problematic formulas. I might be able to spot any obvious flaws
(or questions) in your logic or in the process of copying my formulas.

If you want to try that, send the workbook and explanation to joeu2004
"at" hotmail.com.


----- original message -----

"PAL" wrote in message
...
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)))))))