[Resolved]How to handle divided by 0 error in Excel Formula
In our daily life we use divide formula very often in excel. One common issue we get while using it “#DIV/0!” specially if the value of divisor not known while writing formula.
Let us see the above issue with an example:
download example for more details:
| Sl No | Value1 | Value2 | Formula | Result | Explanation |
| 1 | A2=5 | B2=5 | =A2/B2 | 1 | 5 divided by 5 result 1 |
| 2 | A3=5 | B3=0 | =A3/B3 | #DIV/0! | 5 divieded by 0 giving exception “#DIV/0!” |
Solutions to this is very simple. Use your division inside IF statement.
- IF Function
A brief introduction to IF statement before we use it for above issue:-
IF function have three section as IF(logical Test ,value if true,value if false)
As explain in above line if the logical test will pass 2nd section value will be the output else 3rd section will be the output.
For above scenario whenever divisor is ‘0’ I want output as “NA”, you may opt for any digit or a sentence as per your requirement.
Please refer the example sheet for more deatails:
| Sl No | Value1 | Value2 | Formula | Result | Explanation | |
| 3 | 5 | 0 | =IF(C4=0,”NA”,B4/C4) | NA | “NA” comes as an output when divisor’s(c4) value is 0. |