+5 votes
36 views
in Sell ​​on the Internet by (76.4k points)
reopened
IFERROR in Excel: how to deal with setbacks

1 Answer

+3 votes
by (506k points)
edited
 
Best answer

image

IFERROR in Excel: how to deal with setbacks

The IF conditioner is widely used in Excel to build formulas and determines that an action is only executed if a specific condition is met. As a general rule, this condition refers to values ​​or a sequence of specific characters. But, much more often than desired, Excel issues error messages in a cell. The reason may be that you have misspelled the formula or that it is missing values ​​for a correct calculation. In these cases, you can create an acceptable alternative with the IFERROR function..

Index
  1. What is IFERROR for in Excel?
  2. IFERROR function syntax
  3. IFERROR explanation with examples
    1. Alternative indication with YES.ERROR
    2. IF ERROR and VLOOKUP: avoid # N / A
    3. IFERROR and SUM: Replace errors with a value

What is IFERROR for in Excel?

Whenever Excel is unable to correctly execute a calculation or action, the program will generate an error message in the cell. These messages usually start with a pound sign (#).

  • # N / A - The formula is unable to find what to look for.
  • #VALUE! : an argument has the wrong type.
  • # REF! - The formula refers to an invalid cell.
  • # DIV / 0! - Shown if you try to divide between a cell that is empty or has the value ZERO.
  • # NUM! - The formula expects a numeric value but encounters something else.
  • #NAME? - Usually issued if a typo has been sneaked into the function name.
  • #NULL! : Appears if intersection points that do not exist are assigned.

In most cases, you can try to resolve the underlying problem. But it may also be the case that you already have these types of errors in advance and do not want to solve them. In this case, it is worth making use of Excel's IFERROR function. This function allows you, for example, to replace the error indication with your own message or a value..

This measure not only helps you keep your spreadsheet neater, it also prevents other calculations from being affected . If in another formula you have specified a range of cells where an error message appears, it is possible that this formula generates a new error. To avoid this situation, IFERROR allows you to enter a value that the formula can work with.

Done

Excel also has the ISERROR function. This outputs the values ​​TRUE or FALSE in relation to the occurrence of errors. However, most users integrate it into an SI function. YESERROR saves nesting..

IFERROR function syntax

With the IFERROR formula, the spreadsheet makes it easier than ever. You only need to fill in two arguments: Where can an error arise? What should happen if an error occurs?

  =SI.ERROR(valor; valor_si_error)  

What must be introduced in the arguments?

  • Value : Here is the item to check for an error. You can directly enter a calculation or other function or refer to a cell.
  • Value_if_error : at this point you must enter what should happen in case of error. You can enter numeric values, character sequences, or other functions. If you want a text to be indicated, you must enter it surrounded by double quotes.
Note

In Excel, formulas always begin with the equals symbol (=). If you don't put it, the formula will appear as conventional text in the cell. If this is what you want and you even want the equals symbol to be displayed in the cell as well, you should put a single quote in front of the formula (').

IFERROR explanation with examples

The Excel function can be used in various situations, alone or in combination with other functions.

Alternative indication with YES.ERROR

Probably the easiest way to apply IFERROR to working with Excel is to modify the error notification . In this context, let's imagine a simple calculation: the value in cell A3 is multiplied by the value in cell B3. If in one of the two references there is a letter instead of a number, for example due to a misprint, Excel issues the error message #VALUE !. However, if you link the calculation with the IFERROR function, you can change how this fault is displayed.

  =SI.ERROR (A3*B3; "valores erróneos")  
advice

If you want the cell to remain empty in case of error, enter two quotes in a row.

Now the correct result or your custom error message (in our case? Wrong values?) Will be displayed . If you have multiple calculations of this type, you can apply the formula to all cells.

image
A mistake can be sneaked at any time: with IFERROR you can define a different message.

Instead of replacing the error message, you can also apply IFERROR to another cell and thus create a comment .

  =SI.ERROR (C3; "Aquí hay un error")  

In this case, the function returns the value of C2 even though there is no error. The reason is that the IFERROR function does not know any alternative value . If you want to avoid this circumstance, the combination of YES and ISERROR seems the best option.

  =SI(ESERROR(C2)=VERDADERO; "Aquí hay un error"; "")  
image
Instead of replacing the Excel message, you can also enter a message in another cell.

IF ERROR and VLOOKUP: avoid # N / A

In practical use of Excel, IFERROR is especially useful in combination with the VLOOKUP function. This function is known for regularly throwing the # N / A error . The VLOOKUP function helps you to apply a search function directly on your Excel sheet. However, if you make a mistake in the syntax of the formula or a typo in the search term, Excel issues an error message. SI.ERROR allows you to replace the message # N / A, of very little use, with your own message.

  =SI.ERROR(BUSCARV("Lung, Fritz";A2:C7;2;0); "Introducción errónea")  

Searching for the term? Lung, Fritz? would cause the # N / A error to be displayed, as the name does not appear in the table (since the last argument of VLOOKUP is set to 0, approximate values ​​are not allowed). But, since you've nested the function with IFERROR, your own error message appears instead of the standard message.

image
Enter your own message when the VLOOKUP function does not find the searched object.

If you want to couple multiple VLOOKUP to each other , the IFERROR function is also very useful. Let's imagine you want to search for an object in a table. If the object is not found, you want the following table to be checked and so on. Since VLOOKUP generates an error if it is not possible to find the entered text, IFERROR allows starting a new VLOOKUP.

  =SI.ERROR(BUSCARV(100;A3:B7;2;0);SI.ERROR(BUSCARV(100;D3:E7;2;0);SI.ERROR (BUSCARV(100;G3:H7;2;0);"No encontrado")))  

Every time VLOOKUP does not find the argument searched for and throws an error, the function jumps to the next table. If the value does not appear in any of the tables and all the nesting functions have already been executed, the message you defined will appear.

image
IFERROR allows you to chain several VLOOKUP functions.
Done

Excel also has the SI.ND function. It works like IFERROR, but its activity is limited to the # N / A error message. That is, if you opt for this function instead of the more general function, you will only filter the # N / A error. All other errors will continue to be indicated. This way, you can better react to errors in your formula.

IFERROR and SUM: Replace errors with a value

Let's imagine that you have several calculations and you want to sum the results with the SUM function. If you've made a mistake in your calculations and Excel gives you an error message instead of a numeric value, the addition function may not work correctly either . Therefore, ensure your calculations with IFERROR and indicate the value 0 instead of the error. So the function can execute the task and the sum is not altered.

  =SI.ERROR(A3*B3; 0)  

You also have the option to apply an alternative text only to the SUM function to warn the user of the error.

  =SI.ERROR(SUMA(C2:C7); "Valores erróneos")  
image
The value 0 instead of the error message saves your SUM function.
In summary

Using the IFERROR function you can replace Excel error messages with your own values ​​or messages in different situations. You can also use IFERROR to create complex formulas.


...