AVERAGEIF?

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.