IF(AND(AVERAGE))???

Posted by Eric on September 27, 2001 5:19 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 Travis Harr on September 27, 2001 8:44 PM

One possible way..."=DAVERAGE"

One possible way.
First, lable your data (A4:C4 in my example) and define your criteria in another area (A1:B2 in my example), then use "DAVERAGE". Using your example, see below. Hope that works for you.

--ColumnA--ColumnB--ColumnC
1--color----size
2--blue-----small
3
4--color----size----number
5--blue-----small---84.6
6--red------large---63.4
7--blue-----small---83.4
8--blue-----large---78.4
9--red------small---87.5
10-blue-----small---97.4

=DAVERAGE(A4:C10,3,A1:B2)

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

Eric,

The array formula that is suggested should simply do the job. Also, the following non-array formula will also work:

=SUMPRODUCT((A2:A7="Blue")*(B2:B7="Small")*(C2:C7))/SUMPRODUCT((A2:A7="Blue")*(B2:B7="Small"))

Aladin