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

Status
Not open for further replies.

Shellecj

New Member
Joined
Aug 3, 2013
Messages
32
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!
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Shellecj

New Member
Joined
Aug 3, 2013
Messages
32
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Aug 3, 2013
Messages
32
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. :)
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,114,645
Messages
5,549,156
Members
410,902
Latest member
G Slim
Top