Average & IF & ISERROR

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ryan - 15 Jan 2008 21:57 GMT

I have a working formula currently:

{=AVERAGE(IF($B$20:$B$50=$C17,K$20:K$50))}

However, range K20:K50 can have the error "#DIV/0!".

How do I update my formula to still average my criteria (in this case,
found in C17) and not give me the "#DIV/0!" error?

Sorry if this is an easy fix, I'm trying to teach myself but have had
no luck!

Thanks, in advance, for your help.

Ryan

Reply to this Message

T. Valko - 15 Jan 2008 22:20 GMT

Try it like this (array entered**):

=AVERAGE(IF((B2:B50=C17)*(ISNUMBER(K2:K50)),K2:K50))

Or

=AVERAGE(IF(B2:B50=C17,IF(ISNUMBER(K2:K50),K2:K50)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Signature

Biff
Microsoft Excel MVP

>I have a working formula currently:
>
[quoted text clipped - 11 lines]
>
> Ryan

Reply to this Message

Ryan - 15 Jan 2008 22:29 GMT

That's awesome -- thanks a lot of your help, Biff!

Ryan

> Try it like this (array entered**):
>
[quoted text clipped - 28 lines]
>
> - Show quoted text -

Reply to this Message

T. Valko - 15 Jan 2008 22:47 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

That's awesome -- thanks a lot of your help, Biff!

Ryan

On Jan 15, 5:20 pm, "T. Valko" <biffinp...@comcast.net> wrote:

> Try it like this (array entered**):
>
[quoted text clipped - 32 lines]
>
> - Show quoted text -

Reply to this Message