Excel no doubt is an outstanding tool that helps in performing almost all kinds of analytical tasks smoothly. However, sometimes when a user does not know about the real tactics that must be followed in order to perform a function, errors appear for sure.
Excel uses formulas and when working with formulas, you must have faced the #DIV/0 error. In this post, you will get to know how to remove DIV 0 in Excel. It is called the Division error that often appears when something goes wrong with formulas applied in the operation.
Let’s see how you can simply get rid of this Division error by following some approaches. Before that let’s understand what is divided by Zero errors in Excel.
What Are Excel Divides by Zero Errors?
This logical error appears usually appears when one of the following conditions happens:
- When a number is divided by zero
- When your Excel formula refers to an empty cell
“The formula or function used is dividing by zero or empty cells.”
This is a tooltip that appears when you accidentally click one of the #DIV/0! Cells.
According to this example, in cell E7 you will notice the error because it is divided Cost value in C7 by Catalog Count in D7. Apart from that, another reason why this error appears for cell E11 is that there is no value in D11. It might be assumed that the error appears because it uses the division function, however, it can be taken from the range as well.
How to Remove DIV 0 in Excel with IFERROR?
The IFERROR function provides real-time help when you need to get rid of the #DIV/0! error while working with formulas. Here is the syntax given for the IFERROR formula:
=IFERROR(value, value_if_error)
In this formula, the arguments used are as:
Value – with this formula you will have the division error
Value_if_error – this value is needed when you get an error
Let’s understand this with the help of an example:
- In Column C of the given dataset, you can notice the division formula is present.
- The div error occurs in any instance where the divisor is 0 or blank in column B.
- To get rid of this error, below is the formula you can use:
=IFERROR(A2/B2,””)
- Using the above-mentioned formula, you will get the post-division value if it is not an error. On the other hand, if you get an error, you will see it replaced with the div error with an empty cell. Additionally, if you don’t want an empty cell replacement with the div error, you can denote it as the second argument. Here is another formula that provides the text “Not Available” rather than a division error:
=IFERROR(A2/B2,”Not Available”)
Below are some vital points you must consider when using the IFERROR formula:
- If you know anything about the launch of the IFERROR formula, it comes in Excel 2007 and later versions. Therefore, if you are using Excel 2003 or earlier versions, this formula will not be available.
- You can use the IFERROR formula for all error values. These values include #VALUE!, #NAME, #NUM, #REF!, #DIV/0!, #N/A, etc.
How to Remove DIV 0 in Excel with ISERROR Function?
To get rid of the div 0 error in Excel, you can use the ISERROR nested with the IF function. Nesting is when you get a binary output from any other function such as ISERROR, ISEVEN, ISODD, ISBLANK, etc.
- Here is the formula you can use to remove div error 0 in Excel.
=IF(ISERROR(B2/C2),”0”,B2/C2)
- Drag the fill handle to copy the formula for all other cells. Instant results will appear in terms of the removal of the div 0 error.
- Using the ISERROR function in Excel, you can have an idea if the formula returns the div 0 error or not.
- Type the ISERROR formula and you will get to know whether the division gives an error or not.
- In the third row of the dataset, it shows TRUE because the divisor is 0. Now, you will apply the IF formula nested with ISERROR to see its wonders. In the syntax, the first argument is used for logical testing. The second argument uses to find whether the logical argument is correct or not. If it is true then Excel gives 0 output for the next argument and if it is false, Excel gives an output by dividing the cells.
Notice: Ensure that the values used for the denominator are not zero or empty because it helps in avoiding the #DIV/0! error in Excel.
Summary:
This article explains well enough the procedure of how to remove div 0 in Excel. The #DIV/0! error in Excel appears in return for formulas used incorrectly. Therefore, you need to be vigilant while using formulas.
