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


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?


--
Donovan
------------------------------------------------------------------------
Donovan's Profile: http://www.excelforum.com/member.php...o&userid=35234
View this thread: http://www.excelforum.com/showthread...hreadid=566214

Try...

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

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

Hope this helps!

In article ,
Donovan wrote:

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


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?

You could try this *array* formula:

=Average(IF(K$2:HB$2=G$2,K3:HB3))
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Donovan" wrote in
message ...

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


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?


--
Donovan
------------------------------------------------------------------------
Donovan's Profile:
http://www.excelforum.com/member.php...o&userid=35234
View this thread: http://www.excelforum.com/showthread...hreadid=566214

Youda (u-da) Kings.

Thanks. Works well.


--
Donovan
------------------------------------------------------------------------
Donovan's Profile: http://www.excelforum.com/member.php...o&userid=35234
View this thread: http://www.excelforum.com/showthread...hreadid=566214

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?


--
Donovan
------------------------------------------------------------------------
Donovan's Profile: http://www.excelforum.com/member.php...o&userid=35234
View this thread: http://www.excelforum.com/showthread...hreadid=566214

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:HB30,K3:HB3)))

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

Hope this helps!

In article ,
Donovan wrote:


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?

Tanks again
:)


--
Donovan
------------------------------------------------------------------------
Donovan's Profile: http://www.excelforum.com/member.php...o&userid=35234
View this thread: http://www.excelforum.com/showthread...hreadid=566214