Microsoft Excel shows the #DIV/0! error when a number is divided by zero (0). It happens when you enter a simple formula like =5/0, or when a formula refers to a cell that has 0 or is blank, as shown in this picture.
To correct the error, do any of the following:
-
Make sure the divisor in the function or formula isn't zero or a blank cell.
-
Change the cell reference in the formula to another cell that doesn't have a zero (0) or blank value.
-
Enter #N/A in the cell that's referenced as the divisor in the formula, which will change the formula result to #N/A to indicate the divisor value isn't available.
Many times the #DIV/0! error can't be avoided because your formulas are waiting for input from you or someone else. In that case, you don't want the error message to display at all, so there are a few error handling methods that you can use to suppress the error while you wait for input.
Evaluate the denominator for 0 or no value
The simplest way to suppress the #DIV/0! error is to use the IF function to evaluate the existence of the denominator. If it's a 0 or no value, then show a 0 or no value as the formula result instead of the #DIV/0! error value, otherwise calculate the formula.
For example, if the formula that returns the error is =A2/A3, use =IF(A3,A2/A3,0) to return 0 or =IF(A3,A2/A3,"") to return an empty string. You could also display a custom message like this: =IF(A3,A2/A3,"Input Needed"). With the QUOTIENT function from the first example you would use =IF(A3,QUOTIENT(A2,A3),0). This tells Excel IF(A3 exists, then return the result of the formula, otherwise ignore it).
Use IFERROR to suppress the #DIV/0! error
You can also suppress this error by nesting your division operation inside the IFERROR function. Again, using A2/A3, you can use =IFERROR(A2/A3,0). This tells Excel if your formula evaluates to an error, then return 0, otherwise return the result of the formula.
For versions of Excel prior to Excel 2007, you can use the IF(ISERROR()) method: =IF(ISERROR(A2/A3),0,A2/A3) (See IS functions).
Note: both the IFERROR and IF(ISERROR()) methods are blanket error handlers, in that they will suppress all errors, not just #DIV/0!. You need to make sure your formula works properly before applying any error handling, otherwise you might not realize that your formula isn't working as you expect.
Tip: If error checking is turned on in Excel, you can click 
next to cell that shows the error. Click Show Calculation Steps if it's available, and pick the resolution that works for your data.
Do you have a specific function question?
Help us improve Excel
Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.
No comments:
Post a Comment