Average & IF & ISERROR

[Ryan]

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

 

[T. Valko]

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)

 

[Ryan]

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

Ryan

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

Ryan