Have you ever seen the error named “#N/A” in Excel? Know that it is common for mistakes to appear in this and other ways. At these times, it is essential to know how to deal with and treat these errors.
That’s why, in this post, we’re going to talk about the IFERROR function. You will see that it is essential to handle errors and thus make your spreadsheet perfect.
Table of Contents
What is the IFERROR formula?
IFERROR is an Excel formula used to not show errors — in a polluted way — found in the worksheet. These misconceptions can be in relation to numbers and texts.
Today we are going to talk about the IFERROR function in Microsoft Excel. And, above all, understand what this resource is for.
Surely you must have come across some formulas that have the potential to generate errors several times . For example: a division.
The VLOOKUP function for example has a very large potential to generate errors . Also, it is not good that you leave errors in your spreadsheet or in your reports.
That’s because those who look at your reports can get a bad impression if they come up with errors. That is, it seems that the information that is inserted there has something out of meaning .
Not only is it ugly, it’s also weird. So, you have to know how to handle these errors. Likewise, knowing “what” to display in the cell if any of these errors occur.
So that’s what we’re going to see with the IFERROR function, which is a very simple and extremely useful function .
IFERROR Function Step by Step
The IFERROR function in Excel, as its name says, displays information if an error occurs . Within Excel it is possible to occur several types of error .
So let’s go to the example of the IFERROR function. We have a simple table where we will do some miscellaneous calculations as follows:
- First line: Sum
- Second row: Multiplication
- Third row: Division
- Fourth row: Subtraction
- Fifth line: VLOOKUP function
In this post we will not go into details of the VLOOKUP function because the focus is on the use of the IFERROR function .
The addition, by itself, is very difficult to generate an error as well as multiplication and subtraction. However, if for some reason a text appears in my calculation , it will generate an error .
Still, I say that the addition, multiplication and subtraction functions have no potential to generate an error . That’s because it’s very rare to have a number calculating with text.
In the division, in turn, if I have a perfect calculation, there are no errors. However, if we divide a number by zero it brings the error into the worksheet.
IFERROR Function – VLOOKUP
Likewise, the VLOOKUP function has several possibilities of generating errors . For example: If I enter a Wanted Value that does not belong to the first column of the Array Table, Excel will point out an error in the worksheet.
This happens because Microsoft Excel does not find the information in the matrix of the table that you indicated. For example: We have the item in column 2. Let’s say that I ask Excel to bring information from column 3, however, there is no column 3.
That way, just indicate column 3 and the program will automatically generate a reference error because it doesn’t know what I’m talking about.
IFERROR Function – Division
As we have seen both the VLOOKUP function and the division formula , both have the potential to generate errors . After all, how to deal with it?
Let’s go to the example. We will apply the IFERROR function in division.
We insert the IFERROR function right after the equal sign. This function is very simple as it has only two arguments.
Value : is the operation that has the potential to generate the error. In this case, the division. Therefore, everything that has the potential to generate an error, whether division or VLOOKUP, you will insert in this first argument.
Value if error : here you will enter what you want to appear in case of an error. For example: our error occurs because of division by zero. In this case I will insert the text “Division by zero” . So when the error is this message that will be displayed in my worksheet.
We can see that an error has occurred in the equation. That’s why Excel shows the message instead of the error . If I put in a number again, then it goes back to calculating.
However, if I put the number 0 it returns to display the phrase Division by zero .
Fixing errors in IFERROR function
While it is possible to enter text to indicate any errors, this is not the best alternative for a numeric field . This is because when we are talking about numbers, it is natural that they are used for a calculation.
That is, if I do another column of calculations later I will have problems because I put a text as an alternative to the error .
That way, when Excel performs some calculation with this cell, it will generate another error and we will have to do a series of IFERRORs to eliminate this problem.
For this, it is interesting that you treat on the spot so as not to let the error spread.
Therefore, when working with the IFERROR function, do not use texts as an alternative to the error in numeric fields . Instead, try to enter the number 0 (zero).
This way Excel can calculate other values. In the same way, if you need to make a formula, the program will do the math without generating a chain reaction of errors.
VLOOKUP with IFERRO
PROCV is a separate case. Here there may be no problems in bringing a text into the cell because VLOOKUP is a function that can search for both numbers and texts.
That’s why it’s normal to have texts in cells with product names, for example.
In the example we are going to insert the IFERROR function in the VLOOKUP and at the end we will put the message Code not found
That is, if I enter a number that does not match my VLOOKUP table, this Code not found message will soon appear .
This feature is a way to handle errors and prevent errors from being sent to multiple people via report, print or PDF.
This is because when receiving a file that contains errors, it generates a feeling that something is wrong and one can lose credibility in the person who generates this document.
So here’s the tip of the IFERROR function in Excel. You can use and abuse, I, for example, use it every day mainly in pivot table .
Just be careful what you put as an alternative to the error if it will be text field or numeric field .
Hopefully, you are like this FERROR Function, How to Use This Resource and we are always open to your problems, questions, and suggestions, so feel free to Comment on us by filling this.
This is a free service that we offer, We read every message we receive. Tell those we helped by sharing our posts with friends