How to Handle Division by Zero Error in Excel Using Formulas

In mathematics, division by zero is impossible! One way to explain this rule is by analyzing the process that occurs when one number is divided by another.



Division by Zero Error in Excel

In reality, the division operation is essentially subtraction. For example, dividing 10 by 2 is equivalent to subtracting 2 from 10 multiple times. The subtraction is repeated until the result equals 0. Therefore, the number 2 needs to be subtracted from 10 exactly 5 times:

1. 10-2=8
2. 8-2=6
3. 6-2=4
4. 4-2=2
5. 2-2=0

However, if you try to divide 10 by 0, you will never get a result equal to 0 because subtracting 0 from 10 will always result in 10. Infinite subtractions of zero from ten will never lead to a result = 0. The result will always be the same after the subtraction operation = 10:

• 10-0=10
• 10-0=10
• 10-0=10
• ∞ infinity.

In mathematical circles, the result of dividing any number by zero is called "unbounded." Any computer program attempting division by 0 simply returns an error. In Excel, this error is displayed as the value in the cell #DIV/0!.

However, you can avoid the division by 0 error in Excel. Simply skip the division operation if the denominator is 0. This solution is implemented using the arguments of the =IF() function:

=IF(C2=0,0,B2/C2)

Thus, the Excel formula allows us to "divide" by 0 without errors. When dividing any number by 0, the formula will return the value 0. So, the result after division will be: 10/0=0.

How the Formula for Eliminating Division by Zero Error Works

For the IF function to work correctly, it requires filling in its three arguments:

1. Logical condition.
2. Actions or values to be executed if the logical condition returns TRUE.
3. Actions or values to be executed when the logical condition returns FALSE.

In this case, the argument with the condition contains a check of values. Whether the values in the cells in the "Sales" column are equal to 0. The first argument of the IF function must always have comparison operators between two values to get the condition result as TRUE or FALSE values. In most cases, the equality sign is used as the comparison operator, but other operators can be used, such as greater than (>), less than (<), or their combinations - greater than or equal to (>=), not equal to (!=).

If the condition in the first argument returns TRUE, then the formula will fill the cell with the value from the second argument of the IF function. In this example, the second argument contains the number 0 as the value. So, the cell in the "Performance" column will simply be filled with the number 0 if the cell next to it in the "Sales" column is 0 sales.

If the condition in the first argument returns FALSE, then the value from the third argument of the IF function is used. In this case, the value is formed after the division of the indicator from the "Sales" column by the indicator from the "Plan" column.

Thus, this formula should be read as: "If the value in cell B2 is equal to 0, then the formula returns the value 0. Otherwise, the formula should return the result after the division of values in cells B2/C2."

Formula for Division by Zero or Zero by Number

Let's complicate our formula with the =OR() function. Let's add another sales agent with a zero indicator in sales. Now the formula should be changed to:

=IF(OR(B2=0,C2=0),0,B2/C2)

Copy this formula to all cells in the "Performance" column:

Now, regardless of where there is zero in the denominator or numerator, the formula will work as needed for the user.

Read also: How to Remove Errors in Excel

This function allows us to expand the capabilities of the first argument with a condition in the IF function. Thus, in the cell with the formula D5, the first argument of the IF function should now be read as: "If the values in cells B5 or C5 are equal to zero, then the condition returns the logical value TRUE." And then, how to read the rest of the formula is described above.

Targeted Function for Handling Errors in Excel Formulas

In the next example, we will use the targeted Excel function to handle errors in formula calculation results. Let's model a situation for an example.

A new employee, Alex, starts working in the sales department on the 1st day of the month. He has a trial period - two weeks. During this period, Alex will only get up to speed, and no daily sales plan will be set for him. Based on the results of the work during these two weeks, an individual sales plan will be formed for the new employee. Then, once a week, the plan will change in the direction of increasing. In the last week of the month, the new employee will receive his first monthly sales plan.

Based on these initial values, a data visualization developer needs to build an infographic report on the success of Alex's results. According to the Technical Task, the management requires adding indicators of the level of sales plan fulfillment to the report. It is also necessary to indicate the fact of plan fulfillment in percentages for the current day and for the overall sales history period.

Since there will be no sales plan set in the first 2 weeks, the formula for calculating the percentage of completion will initially return a division by 0 error. To handle the #DIV/0! error, we will use the IFERROR function. The formula in cell J2 looks like this:

=IFERROR(H2/SUM(C3:C33),100%)

In the first argument of the IFERROR function, we use the standard formula for calculating the percentage of sales plan fulfillment. In the second argument, we specify what the function should return if the formula in the first argument returns any error.

As a result, the visualization of the report looks like this:

Download

It is most rational to use the IFERROR function, but sometimes you need to know about alternative solutions for handling #DIV/0! errors, as there are special tasks that require special solutions. Therefore, the article provided various examples with practical application of formulas.