Miracle of IFERROR Function in MS Excel
In this blog, I am going to share my knowledge on iferror() function. Let’s take an example to understand in a better way.
iferror() Function
It is one of the most useful logical function of MS Excel which worked on logical values (True and false). It checks the initial expression and returns an error or we can say returns the second argument. It comes under top 10 formulas of MS Excel. This function is not available on earlier versions of MS Excel 2007. The basic syntax of this function is:
Important Notes:
We have numbers here, Numerators and Denominators. In the last row, we have total sum of these numbers.
78 is the sum of 5+15+22+36 i.e. auto sum function doesn’t add any value for characters. When you will apply the formula “a/b” in column 3 then, the result will be like this:
You can see two errors in column 3. One is #Div/0! And other is #value!
The sum is also showing the error because sum function cannot add these errors. It is a small data but, think about the large data. It is very tough to find the error in a large amount of data and not possible to fix the problem manually. In this case, IFERROR function can be very important.
Now, I will perform the same operation with iferror function: iferror(B5/C5, “”) and press enter. You can write the error message in the quote (“”) sign or leave it blank.
I leave it blank and get the above output. With the help of iferror function, it is easy to value related errors in MS Excel.
Sometimes, functions don’t work in MS Excel due to many reasons like registry problem, corruption in MS Excel file etc. Registry is a huge database where most of the system errors come from. So, you should clean the corrupt registry on a daily basis.
If there is a corruption in MS Excel file, then you can use open and repair option or a reliable third-party tool to fix the corruption.
Conclusion: I described the use of iferror() function with appropriate example. So use this function to test the error and replace it with the meaningful result. Share your thoughts on this post via your valuable comments.
iferror() Function
It is one of the most useful logical function of MS Excel which worked on logical values (True and false). It checks the initial expression and returns an error or we can say returns the second argument. It comes under top 10 formulas of MS Excel. This function is not available on earlier versions of MS Excel 2007. The basic syntax of this function is:
IFERROR(value, “value_if_error”)
Where:
- value: Expression for testing.
- Value_if_error: Error value returned by the function. It evaluated the following error types: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
Important Notes:
- For empty value, it will be an empty string (“”) not an error.
- If user won’t write any message in value_if_error string (“”) then, no error message will be displayed.
We have numbers here, Numerators and Denominators. In the last row, we have total sum of these numbers.
78 is the sum of 5+15+22+36 i.e. auto sum function doesn’t add any value for characters. When you will apply the formula “a/b” in column 3 then, the result will be like this:
You can see two errors in column 3. One is #Div/0! And other is #value!
The sum is also showing the error because sum function cannot add these errors. It is a small data but, think about the large data. It is very tough to find the error in a large amount of data and not possible to fix the problem manually. In this case, IFERROR function can be very important.
Now, I will perform the same operation with iferror function: iferror(B5/C5, “”) and press enter. You can write the error message in the quote (“”) sign or leave it blank.
I leave it blank and get the above output. With the help of iferror function, it is easy to value related errors in MS Excel.
Sometimes, functions don’t work in MS Excel due to many reasons like registry problem, corruption in MS Excel file etc. Registry is a huge database where most of the system errors come from. So, you should clean the corrupt registry on a daily basis.
If there is a corruption in MS Excel file, then you can use open and repair option or a reliable third-party tool to fix the corruption.
Conclusion: I described the use of iferror() function with appropriate example. So use this function to test the error and replace it with the meaningful result. Share your thoughts on this post via your valuable comments.
Comments
Post a Comment