similar to sumif() but for averages

[Donovan]

Does anyone know how to create a formula similar to sumif() but for averages.
I.o.w. =averageif() with criteria

Hence:

I have a:

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

The ideal 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?

[Domenic]

Try...

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

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

Hope this helps!

In article <[email protected]>,
Donovan <[email protected]> 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?

[Ragdyer]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Donovan]

Youda (u-da) Kings.

Thanks. Works well.

[Donovan]

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?

[Domenic]

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!

In article <[email protected]>,
Donovan <[email protected]> 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?

[Donovan]

Tanks again