It is such a blessing to have formulae as a built-in feature in MS Excel, but that doesn’t guarantee to deliver the correct results every time. There may be moments of derailing which would lead to errors taking up the very place where the results are to be displayed. Errors of such kind are numerous in MS Excel.
One such error is the #DIV/0 error & we shall look into it elaborately in this article covering both its cause and the steps to tackle it. Following is the dataset that we would be using for the purpose of demonstrating it.
Why #DIV/0 happen?
Let us start by understanding what causes this error in the first place. Looking into the error code, one can figure that it has to do with something that is divided by zero & that is exactly what it stands for. When one tries to perform a division operation & the cell which has been given reference for the denominator has nothing, then this error would show up. That’s good chap Excel alerting us that ‘What you have given is insufficient Sir’.
Let us now take a closer look at the below image.
The cell displaying the error can be traced back to its underlying formula which in turn leads us to the cell reference for the denominator – M4 which has zero as its value. So, will removing the zero shoo this error off the cell? Let us find out!
Seems that it doesn’t!
Handling the #DIV/0 Error:
We shall put into use each of the following ways to sort out this error.
- Method 1 – Put some number
- Method 2 – Use the IFERROR Statement
Method 1 – Add a number:
This is a no-brainer! The #DIV/0 error is displayed only if there is no numerical content or value 0 in the denominator or the cell that is given as a reference for the denominator. So, one can prevent this from happening by making sure that only numerical content is being entered in all the cells that are referred to as denominators in the formula.
The term ‘numerical content’ is emphasised here since entering data in textual or a date format in these cells would also result in the #DIV/0 error.
Method 2 – IFERROR Statement:
This is a sophisticated way of dealing with the #DIV/0 error. Before constructing the formula for division, one ought to start with the IFERROR statement similar to the one shown below.
As with all the formulae, start with an equals sign (=) & type IFERROR exactly in the same way given; all in caps & without any spaces. Once done, include an open parenthesis. One shall now construct the formula for division following this open parenthesis.
Once the division formula is constructed as shown above include a comma (,) immediately following it & now comes the portion which one can customise to suit one’s requirements. This part of the formula gets triggered and does whatever is stated in this portion, only if any error surfaces.
Say, for instance, we decide to provide a statement “Not Available” whenever the formula computation results in an error. It has to be typed in the same way within the double quotes as indicated above. Then a closing parenthesis shall be included to complete the IFERROR statement.
Hit ENTER & witness the magic!
Conclusion:
Now that we have reached the end of this article, here’s another that details how to change a series name in MS Excel. There are numerous other articles too in QuickExcel that can come in handy for those who are in looking to level up their skills in MS Excel. Cheers!