Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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 introduced in Excel 2007, and 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%))

Example of a well-constructed IF statement

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”.

Excel message when you add comma to a value

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

See Also

Correct a #VALUE! error

IF function

IFERROR function

IS functions

IFS function (Microsoft 365 or Excel 2016 or later)

IF function – nested formulas and avoiding pitfalls

Video: Nested IF functions

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

All Excel functions (alphabetical)

All Excel functions (by category)

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×