Formula Excel : Dealing with Divide-by-Zero Errors

In mathematics, division by zero is impossible. One way to understand why it’s impossible is to consider what happens when you divide a number by another. Division is really nothing more than fancy subtraction. For example, 10 divided by 2 is the same as starting with 10 and continuously subtracting 2 as many times as needed to get to zero. In this case, you would need to continuously subtract 2 five times.

• 10-2 = 8 
• 8-2 = 6 
• 6-2 = 4 
• 4-2 = 2 
• 2-2 = 0
  So, 10/2 = 5.

Now if you tried to do this with 10 divided by 0, you would never get anywhere, because 10-0 is 10 all day long. You’d be sitting there subtracting 0 until your calculator dies.

• 10-0 = 10 
• 10-0 = 10 
• 10-0 = 10 
• 10-0 = 10
  …..Infinity

Mathematicians call the result you get when dividing any number by zero “undefined.” Software like Excel simply gives you an error when you try to divide by zero. In Excel, when you divide a number by zero, you get the #DIV/0! error. You can avoid this by telling Excel to skip the calculation if your denominator is a zero. Image 2-9 illustrates how to do this by wrapping the division operation in Excel’s IF function.

=IF(C4=0, 0, D4/C4)

Image 2-9: Using the IF function to avoid a division-by-zero error.

How it works

The IF function requires three arguments: the condition; what to do if the condition is true; and what to do if the condition is false.

The condition argument in this example is the budget in C4 is equal to zero (C4=0). Condition arguments must be structured to return TRUE or FALSE, and that usually means that there is a comparison operation (like an equal sign or greater-than sign).

If the condition argument returns TRUE, the second argument of the IF function is returned to the cell. The second argument is 0, meaning that you simply want a zero displayed if the budget number in cell C4 is a zero.

IF the condition argument is not zero, the third argument takes effect. In the third argument, you tell Excel to perform the division calculation (D4/C4). So this formula basically says that if C4 equals 0, then return a 0, or else return the result of D4/C4.

Categories: Formula