IF is one of the most versatile and popular functions in Excel, and is often used multiple times in a single formula, as well as in combination with other functions. Unfortunately, because of the complexity with which IF statements can be built, it is fairly easy to run into the #VALUE! error. You can usually suppress the error by adding error-handling specific functions like ISERROR, ISERR, or IFERROR to your formula.
Problem: The argument refers to error values
When there is a cell reference to an error value, IF displays the #VALUE! error.
Solution: You can use any of the error-handling formulas such as ISERROR, ISERR, or IFERROR along with IF. The following topics explain how to use IF, ISERROR and ISERR, or IFERROR in a formula when your argument refers to error values.
Notes:
-
IFERROR was is far more preferable to ISERROR or ISERR, as it doesn’t require a formula to be constructed redundantly. ISERROR and ISERR force a formula to be calculated twice, first to see if it evaluates to an error, then again to return its result. IFERROR only calculates once.
-
=IFERROR(Formula,0) is much better than =IF(ISERROR(Formula,0,Formula))
Problem: The syntax is incorrect
If a function’s syntax is not constructed correctly, it can return the #VALUE! error.
Solution: Make sure you are constructing the syntax properly. Here’s an example of a well-constructed formula that nests an IF function inside another IF function to calculate deductions based on income level.
=IF(E2<31500,E2*15%,IF(E2<72500,E2*25%,E2*28%))
In simple English this means - IF(the value in cell A5 is less than 31,500, then multiply the value by 15%. But IF it's not, check to see if the value is less than 72,500. IF it is, multiply by 25%, otherwise multiply by 28%).
To use IFERROR with an existing formula, you just wrap the completed formula with IFERROR:
=IFERROR(IF(E2<31500,E2*15%,IF(E2<72500,E2*25%,E2*28%)),0)
Which simply says IF any part of the original formula evaluates to an error, then display 0, otherwise return the result of the IF statement. Some people write their formulas complete with error handling to start, however this isn’t good practice, since the error handler will suppress any potential errors, so you won’t necessarily know if your formula is working properly or not. If you need to add error handling, it’s best to add it once you’re sure your formula works properly.
Note: The evaluation values in formulas don't have commas. If you add them, the IF function will try to use them as arguments and Excel will yell at you. On the other hand, the percentage multipliers have the % symbol. This tells Excel you want those values to be seen as percentages. Otherwise, you would need to enter them as their actual percentage values, like “E2*0.25”.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
See Also
IFS function (Microsoft 365 or Excel 2016 or later)
IF function – nested formulas and avoiding pitfalls
