Is there an AVERAGEIF function in excel?

  • Thread starter Thread starter lisab
  • Start date Start date
L

lisab

I'm trying to average the values in one column if another column's value is
true. I know there is a sumif and a countif, but couldn not find an
averageif. Any help would be much appreciated!
 
There is an AVERAGEIF function in Excel 2007 but not in earlier versions. If
you looked for it and didn't find it then you probably aren't using Excel
2007.
average the values in one column if another
column's value is true.
Click to expand...

Assuming the true's are Boolean (logical) TRUE and not TEXT true.

This array formula** will work in any version.

Assuming that the range with TRUE doesn't contain any text or numbers.

=AVERAGE(IF(B1:B20,A1:A20))

Where A1:A20 are the values to average.B1:B20 is the range that might
contain TRUE.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Hi,

If you have excel 2007 then there is an averageif

=AVERAGEIF(A1:A20,"Sometext",B1:B20)


If you don't then there are several ways. here's an array formula way
=AVERAGE(IF(A1:A20="Sometext",B1:B20))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
 
You must be using 2003. AVERAGEIF() is available from 2007 version onwards.

Try the array formula. If a2:A10 = E2 then average C2:c10
=AVERAGE(IF((A2:A10=E2),C2:C10))

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

If this post helps click Yes
 
You must be using 2003.

There are versions of Excel before Excel 2003! I use one of them!
 
As Mike H and Jacob have mentioned, there are several ways to overcome this.
IMO, it is an unneeded function as you can simply do:

=SUMIF(A:A,"Condition",B:B)/COUNTIF(A:A,"Condition")

Note that this is not an array formula.
 
Back
Top