How to Ignore #DIV/0! Values in Excel

This guide will explain how to ignore #DIV/0! values in Excel using the IFERROR function

The rules for using the IFERROR function in Excel are the following:

  • The IFERROR function is used to return a custom result when a formula results in an error. Additionally, the function returns a standard result when no error is detected. 
  • When the value argument is empty, the function will evaluate it as an empty string and not an error.
  • When the value_if_error is inputted as an empty string, the function will have no message displayed or the cell will be blank when an error is detected.
  • When a calculation has no errors and returns a normal result, the function will return that result,
  • When a calculation returns an error, the function will return an alternative result. 
  • The IFERROR function checks for errors such as #N/A, #VALUE!, #DIV/0!, #REF!, #NUM!, #NULL!, or #NAME?. 

Since it has several built-in functions and tools, Excel has become a popular tool that people use to easily perform long and complicated calculations. However, we cannot avoid errors when doing calculations.

When an error is made during a calculation, many error values are returned. Usually, it would be difficult to continue the calculation if an error is spotted. But, there are still ways for us to ignore the errors and continue with the calculations.

In this guide, we will focus on ignoring #DIV/0! error values. So the #DIV/0! error value occurs when we attempt to divide a value by zero. Luckily, we can ignore the #DIV/0! error value with the use of the IFERROR function.

Let’s take a sample scenario wherein we need to ignore the #DIV/0! error value in Excel.

Suppose you are performing a series of calculations in Excel. And you noticed that some results returned a #DIV/0! error value. Once you traced back, you noticed that the value was divided by zero. However, you only wanted to ignore the error and return zero as a result instead.

To do this, you used the IFERROR function to fix the error and return a result of zero instead of the #DIV/0! error.

Before we move on to a real example of ignoring #DIV/0! error values, let’s first learn the syntax of the IFERROR function.

 

The Anatomy of the IFERROR Function

The syntax or the way we write the IFERROR function is as follows:

=IFERROR(value, value_if_error)

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we start any function in Excel.
  • IFERROR() refers to our IFERROR function. And this function returns an alternative result if the expression is an error. Otherwise, it will return the normal result if the expression has no error.
  • value is a required argument. So it refers to any value or expression, or cell reference. And this is the value that will be returned if no error is found.
  • value_if_error is another required argument. And this also refers to any value, expression, or cell reference which will be returned if an error is found in the expression.

Great! Now we can move on and dive into a real example of ignoring #DIV/0! error values in Excel using the IFERROR function.

 

A Real Example of Ignoring #DIV/0! Values in Excel

Let’s say we have a data set containing two values that are being divided. So there are three columns. And the first two columns each contain values being divided, while the third column displays the results. However, there are some #DIV/0! results. So our initial data set would look like this:

Initial dataset

 

When we try to divide a value by zero, Excel will return a #DIV/0! error value. But, there are times when we want to continue with the calculation and not change anything about the values. 

To fix this issue, we can simply ignore the #DIV/0! error value using the IFERROR function. Instead of the #DIV/0! error value, we want the result to be zero. And we can easily do this using the IFERROR function.

So the IFERROR function is used to trap and handle errors. In this case, the function will return an alternative result when an error is detected in the calculation, expression, or formula. 

Furthermore, we can also customize a different result when an error is found. For instance, we can also return a blank cell instead of a zero when #DIV/0! error values are found. To do this, we can simply input empty strings in the formula (“”). And this will return a blank cell when an error is found.

Additionally, we can also return a text value to display a message that an error is found. And this is a great way to explain why an error is found in certain rows in the data set.

For example, we want to display the message “value was divided by zero” in the rows where #DIV/0! error values are found. So we will apply this formula with the IFERROR function in our data set to ignore the #DIV/0! error values. 

So our final data set would look like this:

Final dataset

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can proceed and explain the steps of how to ignore #DIV/0! error values in Excel using the IFERROR function.

 

How to Ignore #DIV/0! Values in Excel

In this section, we will explain the step-by-step process of how to ignore #DIV/0! error values in Excel using the IFERROR function. Additionally, each step has detailed instructions and pictures to help you through the process.

To apply this method to your work, we can simply follow the process below. 

1. Firstly, let’s erase the previous results in the third column of the data set so we can input the new formula to ignore the #DIV/0! error values. In this case, we want the formula to return zero when #DIV/0! error values are detected. To do this, we can simply type in the formula “=IFERROR(B2/C2, 0)”. 

Then, we can press the Enter key to return the result.

Ignore #DIV/0! Values in Excel

 

2. Secondly, we can drag down the Fill Handle tool to copy the formula and apply it to the other cells.

Drag down fill handle tool

 

3. Thirdly, let’s try to return a blank cell when #DIV/0! error values are found. To do this, we can simply input the formula “=IFERROR(B2/C2, “”)”. Next, we will press the Enter key to return the result.

Ignore #DIV/0! Values in Excel

 

4. Then, we can drag down the Fill Handle tool to copy the formula and apply it to the rest of the cells.

Drag fill handle tool

 

5. Lastly, let’s try displaying a message when #DIV/0! error values are found in the data set. To do this, we can simply type in the formula “=IFERROR(B2/C2, “value was divided by zero”)”. Finally, we will press the Enter key to return the result.

Ignore #DIV/0! Values in Excel

 

6. Afterward, we can drag the Fill Handle tool down to copy and apply the formula to the column.

Ignore #DIV/0! Values in Excel

 

7. And tada! We have successfully ignored #DIV/0! error values in Excel.

Ignore #DIV/0! Values in Excel

 

And that’s pretty much it! We have successfully explained how to ignore #DIV/0! values in Excel using the IFERROR function. Now you can apply this method to your own work whenever you encounter these error values, and you simply want to continue your calculations. 

Are you interested in learning more about what Excel can do? You can now use the IFERROR function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like