similar to sumif() but for averages

  • Thread starter Thread starter Donovan
  • Start date Start date
D

Donovan

Does anyone know how to create a formula similar to sumif() but for
averages.
:confused:

Hence:

I have a:

- range
- criteria
- AVG_range (as opposed to a sum_range)


The formula that I need would be =averageIF(K$2:HB$2,G$2,K3:HB3) which
is similar to =SUMIF(K$2:HB$2,G$2,K3:HB3) but does the averages based
on the criteria.

Are there any experts out there who knows how to do this?
 
Try...

=AVERAGE(IF(K$2:HB$2=G$2,K3:HB3))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
:confused:
While I got exited at first, it may have been premature. Not sure if
ya'll can help wrap this one out?

It seems the answer for {=Average(IF(K$2:HB$2=G$2,K3:HB3))}
is wrong. It appears to include blank data as zero's in the
calculation and therefore lower the average to a level lower than it
should be.

In other words, blank data should not be part of the AVG

Note: The average I guess should be when the data is greater than
Zero.

Anyone able to help?
 
To ignore blanks...

=AVERAGE(IF(K$2:HB$2=G$2,IF(K3:HB3<>"",K3:HB3)))

To average numbers greater than 0...

=AVERAGE(IF(K$2:HB$2=G$2,IF(K3:HB3>0,K3:HB3)))

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Back
Top