Need help using ERROR.TYPE in an IF statement (not getting antipated result if the condition is false)

[Shellecj]

Hello,

I am trying to figure out why the formula's below work if the first condition is TRUE (in this case returns the ERROR.TYPE 2 for #DIV/0) the formula will return a "0" as requested (in Example A below); however, if the first condition of the if formula is FALSE it returns N/A?

I have two array formula's where in the first example was returning a #DIV/0 because no matches were found and the average part of the formula was resulting in the #DIV/O. So I amended the formula to check to see if the ERROR.TYPE was 2 then to return a 0 in the cell instead. This works perfectly. However the 2nd formula (example B below) was returning 136 but I also amended the formula to check for ERROR.TYPE 2 (as a precaution in case when updating my sheets with all new data pulled for the month and there weren't any matches where the customer did NOT equal the account of 002). If I go in to analyze the formula in audit mode and hit F9 on the first part of the 2nd formula below (ex B), it shows N/A instead of FALSE, which results in getting N/A as the final result. I am confused why excel is not jumping to the second part of the formula (the value if false) to return what should be 136? How do I get the first part (the value if TRUE) to return FALSE if it's NOT an ERROR.TYPE 2, instead of #N/A???

Example A:
{=IF(ERROR.TYPE(AVERAGE(IF(P3:P100="002",IF(C3:C100>=$A$1,IF(C3:C100<$B$1,D3:D100)))))=2,0,AVERAGE(IF(P3:P100="002",IF(C3:C100>=$A$1,IF(C3:C100<$B$1,D3:D100)))))}

Example B:
{=IF(ERROR.TYPE(AVERAGE(IF(P3:P100<>"002",IF(C3:C100>=$A$1,IF(C3:C100<$B$1,D3:D100)))))=2,0,AVERAGE(IF(P3:P100<>"002",IF(C3:C100>=$A$1,IF(C3:C100<$B$1,D3:D100)))))}

I am aware that I could also use functions like IF(ISERROR IF(ISERR or even IFERROR however I'm afraid doing this could possibly lead to other unexpected problems in the future if the error is due to something else (as stressed in this article here Excel Formula Errors & How To Deal With Them). In this case, I only want to mask the error if it's #DIV/0, not #N/A, #REF etc.

Any help or clarification would be greatly appreciated...thanks!

 

[Jonmo1]

Since you mentioned IFERROR as one of the possible solutions, you must have xl2007+
So I'd first suggest using AVERAGEIFS instead of the array formula Average(If

=AVERAGEIFS(D3:D100,P3:P100,"002",C3:C100,">="&$A$1,C3:C100,"<"&$B$1)

I know that's not solution, bear with me.
Better to use built in functions than array entered functions if possible..


Now, since you want to test specifically for #Div/0 Errors, you don't actually have to test for error at all.
Instead, test for what CAUSES #Div/0 errors.

Since the function you're doing is an Average (Average = Sum/Count)
The Cause of the #Div/0 Error is when the count is 0.
So test for that...

Try

=IF(COUNTIFS(P3:P100,"002",C3:C100,">="&$A$1,C3:C100,"<"&$B$1)=0,0,AVERAGEIFS(D3:D100,P3:P100,"002",C3:C100,">="&$A$1,C3:C100,"<"&$B$1))

 

[Shellecj]

Re: Need help using ERROR.TYPE in an IF statement (not getting anticipated result if the condition is false)

Brilliant! This does work! Thanks! Couple questions though. I'm pretty sure I've used this formula in the past but don't remember putting and & (ampersand) in front of the cell reference (which holds a date) though? What is the purpose of the &ampersand?

As for the original formula I posted, do you know why it wasn't working? I'll most likely go with the suggestion you just posted since it looks to be working fine but was just curious about where I went wrong with my original formula? In the link to the article I posted, it had suggested handling the #DIV/0 errors this way but didn't clarify what the result would be if the condition was false?

Thank you so much for the quick response, btw! Much appreciated!

 

[Jonmo1]

Re: Need help using ERROR.TYPE in an IF statement (not getting anticipated result if the condition is false)

Here is the description of the Error.Type function quoted from the help files

Description

Returns a number corresponding to one of the error values in Microsoft Excel or returns the #N/A error if no error exists. You can use ERROR.TYPE in an IF function to test for an error value and return a text string, such as a message, instead of the error value.

So if the function in the Error.Type() function is NOT an error, then Error.Type returns #N/A
Which in my opinion kinda makes it nearly useless.


the & is used is because the CountifS (and sumifs/averages etc..) expects a number expression or text string to compare against the range.
It can't use > or < symbols directly, they have to be expressed as a string like ">10".
And you can't do ">A1" because there it just sees A1 as literally a text string "A1"
So you have to concatenate the value contained in A1 into the string expression. ">"&A1
That becomes ">10" assuming A1 contains 10



Hope that helps.

 

[Shellecj]

Re: Need help using ERROR.TYPE in an IF statement (not getting anticipated result if the condition is false)

Yes, that did! Great explanation! Thank you so much!!!

 

[Jonmo1]

Re: Need help using ERROR.TYPE in an IF statement (not getting anticipated result if the condition is false)

Glad to help, thanks for the feedback.