IF(AND(AVERAGE)) - Is it Possible???

Posted by Eric on September 27, 2001 2:59 PM

I've been trying to figure this out for a while now.

Column A Column B Column C
A 2 84.6
B 2 85.5
B 3 93.1
A 2 73.4
A 3 73.8
B 3 96.4
A 2 87.0

Now this is my problem, I want the AVERAGE from Column C, IF Column A is "A", AND Column B is "2". Is this possible? Any ideas would be greatly appreciated, I've exhausted all of my own.

Thanks

Posted by IML on September 27, 2001 3:07 PM

Try the following array (hit enter while control and shift are depressed:
=SUM((A1:A7="a")*(B1:B7=2)*(C1:C7))/SUM((A1:A7="a")*(B1:B7=2))

Posted by Eric on September 27, 2001 3:32 PM

Thanks, but I think my example was unclear. Columns A & B are not numerical values, the value of Column C comes from a different formula. Let me give a better example:

ColumnA-ColumnB-ColumnC
Blue----Small---84.6
Red-----Large---63.4
Blue----Small---83.4
Blue----Large---78.4
Red-----Small---87.5
Blue----Small---97.4

Looking for the AVERAGE of Column C, IF Column A is "Blue" AND Column B is "Small"? I think this is a better example. Thanks again for any replies.

Posted by Aladin Akyurek on September 27, 2001 9:53 PM

Three Alternatives

Eric --

The array formula IML suggests will simply do what you want: modify it to fit your new example as

=SUM((A1:A7="Blue")*(B1:B7="Small")*(C1:C7))/SUM((A1:A7="Blue")*(B1:B7="Small"))

Don't forget using CONTROL+SHIFT+ENTER to enter it.

You can also use SUMPRODUCT version suggested at

32178.html

or DAVERAGE suggested at

32178.html

Aladin