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, understandwhatthis resource is for.
Surely you must have come across some formulas that havethe potential to generate errorsseveral times . For example: a division.
The VLOOKUP function for example has a very large potential to generate errors . Also,it is not goodthat youleave errors in your spreadsheetor in your reports.
That’s because those who look at your reportscan get a bad impressionif they come up with errors. That is, it seems that theinformationthat is inserted there has somethingout of meaning.
Not only is it ugly, it’s also weird. So, you have toknow how to handle these errors.Likewise,knowing “what” to display in the cellif any of these errors occur.
So that’s what we’re going to see with the IFERROR function, which is avery 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 occurseveral types of error.
So let’s go to the example of the IFERROR function. We have a simple table where we will do somemiscellaneous calculationsas follows:
- First line: Sum
- Second row: Multiplication
- Third row: Division
- Fourth row: Subtraction
- Fifth line: VLOOKUP function
In this post we will not go intodetails of the VLOOKUP functionbecause thefocusis on the use of theIFERRORfunction .
The addition, by itself, is very difficult to generate an erroras well as multiplication and subtraction. However, if for some reasona text appears in my calculation, it will generatean error.
Still, I say that theaddition, 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 errorinto the worksheet.
IFERROR Function – VLOOKUP
Likewise, theVLOOKUP 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 Exceldoes not find the information in the matrix of the tablethat 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 automaticallygenerate a reference errorbecause it doesn’t know what I’m talking about.
IFERROR Function – Division
As we have seen both theVLOOKUP function and the division formula, both have thepotential 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 Excelshows 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 phraseDivision by zero.
Fixing errors in IFERROR function
While it ispossible to enter textto indicate any errors, thisis 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 haveproblemsbecause I put atext as an alternative to the error.
That way, when Excelperforms some calculation with this cell, it will generate another errorand we will have to do a series of IFERRORs to eliminate this problem.
For this, it is interesting that youtreat on the spotso as not to let the error spread.
Therefore, when working with the IFERROR function, donot 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 messageCode not found
That is, if I enter a number that does not match my VLOOKUP table, thisCode not foundmessage will soon appear .
This feature is a way to handle errors andprevent errorsfrom being sent to multiple people via report, print or PDF.
This is because when receiving a file that contains errors, itgenerates a feeling that something is wrongand one canlose credibilityin 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.
Justbe carefulwhat you put asan 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