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!
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!