How to correct a #VALUE! error in the IF function

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.


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:


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

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.

See Also

Correct a #VALUE! error

IF function

IFERROR function

IS functions

IFS function (Office 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

Use error checking to detect errors in formulas

All Excel functions (alphabetical)

All Excel functions (by category)

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!