You can use the following array formula. It assumes you are looking to average the numbers in B1:B10 where "dog" is next to it in column A.
=AVERAGE(IF(A1:A10="dog",B1:B10))
For this to work, you must hit enter while control and shift are depressed.
Good luck.
Try this:
{=AVERAGE(IF(A2:A200>2,A2:A200,FALSE))}
Note that it is an array formula, so you must Ctrl + Shift + Enter it.
"A2:A200>2" is your condition. The second "A2:A200" is the range that it will average/exclude from the average if the cells satisfy/don't satisfy your condition. The FALSE tells the formula to exclude a cell from the average if it doesn't meet your condition. Be sure to use FALSE here, because if you use Zero or Null it will include those in the average.
-Ben
Beaten to the punch! :) (n/t)
Brian
I know for sure Ben as well as Ian wouldn't mind the caveat that follows.
If you have multiple conditions such as if invoice date >= Jan 15, 01 and Company = S & A Sales, compute average invoice-amount, then it is preferable to use an array-formula.
If you have a single condition that has to determine the average, a combination of SUMIF and COUNTIF might be preferable, especially when you have lots of data records.
How? Consider the following sample data
{"a",6;"b",5;"c",4;"c",3}
that occupy A1:B4.
lets say that you want to average all values in B that is associated with a "c" value in A. The formula that follows will do it:
=SUMIF(A1:A4,"c",B1:B4)/COUNTIF(A1:A4,"c")
or, better:
=IF(COUNTIF(A1:A4,"c")>0,SUMIF(A1:A4,"c",B1:B4)/COUNTIF(A1:A4,"c"),"")
Aladin
Absolutely - I was in an array mind-set (rut?) nt
: I'm looking for a way to make a formula functionally equivalent to SUMIF but instead of summing, it averages.