Average If
Posted by David on November 30, 2001 7:59 PM
I'd like to average the numbers in column B if certain criteria is met in the adjacent cell in column A. For instance, assume A1:A6 is red, blue, red, blue, red, blue and B1:B6 is 2,4,6,3,5,7. What formula could I put in a cell in column C that would average only the numbers in B that have "blue" next to them in A? Is it possible?
Posted by Johnny on November 30, 2001 8:20 PM
Got one solution to your problem if you can use an extra column (that you can hide):
In the hidden column (example D1)
=If(A1="blue";B1;"") That will give you the number next to blue.
In C, just put in =Average(D1:D6) and you have your average of the numbers next to blue.
Probably many other solutions to this this is the one I came up with.
Johnny
Posted by Aladin Akyurek on November 30, 2001 10:12 PM
David --
=SUMIF(A1:A6,"blue",B1:B6)/MAX(1,COUNTIF(A1:A6,"blue")
Aladin
Posted by David on December 01, 2001 8:51 AM
Thanks! - that does the trick!!!
