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!

 

[Shellecj]

Please do not reply to this thread...I hit back space on my browser and inadvertently created a duplicate of my post. Please see other post under same thread title.

 

[Aladin Akyurek]

I'm afraid the article is wrong when it states:

=IF(ERROR.TYPE(A1/A2)=2,0,A1/A2)

for this function is designed to return #N/A if there is no error.

Thus:

IF(#N/A=2,0,A1/A2)

will end up in #N/A, not in the result of A1/A2.

By the way:

=IF(ISNUMBER(AVERAGE(...)),AVERAGE(...),0)

or

=IFERROR(AVERAGE(...),0)

cannot or do not propagate any error to future or other calculations.

 

[Shellecj]

Thanks Aladin! Can you post this to the other thread for all to see though, since it's helpful? This thread is a duplicate I inadvertently created by hitting the backspace on my browser. I've asked moderator to delete this thread, so might not be here tomorrow.

 

[xenou]

Refer to related thread:
http://www.mrexcel.com/forum/excel-...ed-result-if-condition-false.html#post3596160