When you’ve done some computations on your Excel sheet, you sometimes look at the whole thing later and find that one of the cells has a #DIV/0! error. Sounds fancy, right? Well, it’s actually quite simple.
We’re going to walk you through what the #DIV/0! error is, why it occurs, and the ways to fix it.
What is #DIV/0! Error in Excel?
The #DIV/0! error tells you that you’ve divided a number on your worksheet by 0, or a value that’s equivalent to 0 (like a blank cell). Since dividing a number by 0 is mathematically undefined, Excel returns an error. The good thing is, fixing it is fairly straightforward.
Before we talk about fixes though, let’s talk about what causes the #DIV/0! error.
Why Does it Appear?
#DIV/0! error can occur because of two reasons.
The first reason is that you’ve divided a number by 0, or a value that’s equivalent to 0. Since the value is undefined, Excel can’t output a valid answer. Therefore, it returns a #DIV/0! error.
The second reason could be that you’ve referenced a cell in one of your formulas, which already has a #DIV/0! error. For instance, say cell A2 in your worksheet has a #DIV! error. But you’ve referenced it in cell D2 and multiplied it to some other value. The output in cell D2 as well will be #DIV/0!.
Okay, so say you’ve got a few #DIV/0! errors on your worksheet because of both of these reasons. How do you solve them? It’s a piece of cake. Let’s walk through some examples and see how you can eliminate #DIV/0! errors.
Examples
Following are some examples of how to fix a #DIV/0! error in Excel.
1. Fix #DIV/0! error by correcting the values
The simplest way to get rid of a #DIV/0! error is to correct the error you’ve committed. However, it’s only practical when the problem is fairly straightforward. For instance, say you’ve applied the AVERAGE function to a range of blank cells.
=AVERAGE(A2:A6)
The way to fix it? Just make sure that there are no blank cells in the referenced cell range A2:A6. Of course, the values need to be numeric.
Another thing you should note is that when you divide a number by a text string, you’ll see a #VALUE! error. However, when you apply the AVERAGE function to a range of cells containing text values, you’ll still get a #DIV/0! error.
The reason? Go back to the fundamental reason why the #DIV/0! error occurs—a 0 in the denominator, correct?
The AVERAGE function equals SUM/COUNT. When applied to a cell range containing text strings, the SUM function returns, and so does the COUNT function. So, you’ve got a 0 in the denominator again, which causes a #DIV/0! error.
The #DIV/0! error is an old pal of averages on Excel. It’s not just the AVERAGE function that can give you a #DIV/0! error. The AVERAGEIF and AVERAGEIFS functions can both give you #DIV/0! errors in certain circumstances.
Ever heard of named ranges? Say you’ve got a few of them on your worksheet.
Here, range B2:B9 is named as 'Population', and range A2:A9 is named as 'Animals'. Over these named ranges you’ve applied the AVERAGEIFS function as shown in the next image, but you’re getting a #DIV/0! error.
Again, the function returns #DIV/0! error for the animal Donkey because there are no matching items on the list, and therefore the count for "Donkey" would be 0. However, sometimes, finding the cause of the error can become a lot more complex. That’s when you resort to trapping the #DIV/0! error.
2. Fix #DIV/0! error by trapping it with the IF function
The IF function is a great way to bypass an error. When you have a #DIV/0! error (or multiple #DIV/0! errors), you can use the IF function to trap the error and get a clean output on your worksheet.
Let’s use a simple example to see how this works. Say you’ve got a number in cell A2, and a blank cell A3. You’ve divided these two numbers in cell B2, which obviously, returns a #DIV/0! error.
However, you now replace the formula in cell B2 with the following formula.
=IF(ISBLANK(A3),"",A2/A3)
The formula basically asks Excel to return a blank output if the denominator (i.e., cell A3) is blank. If it’s not blank, Excel will return an actual answer. But if cell A3 contains a 0, you’ll still get a #DIV/0! error. Sure, you can solve this by creating an even more complex formula, but there’s a simpler way.
3. Fix #DIV/0! error by trapping it with the IFERROR function
The simpler way to trap the #DIV/0! error is with the IFERROR function. The function pretty much traps any error and instead returns a value that you have entered as an argument in the formula.
Continuing the previous example, say that you’ve got a numeric value and a blank cell. Dividing them has resulted in a #DIV/0! error. There’s no need to wear your smart hat and craft a giant formula. Just use the following formula instead.
=IFERROR(A2/A3,"")
Simple, eh?
The formula tells Excel that if dividing A2/A3 results in any type of error, just return a blank character. Of course, you can add a message or an instruction instead of a blank character. For instance:
=IFERROR(A2/A3,"Check your inputs")
That’s all you need to know about eliminating the #DIV/0! error from your worksheet. While you solve your #DIV/0! errors, we’ll load up some more tutorials to make sure your Excel rides are buttery-smooth.
About Supriya