-2

I've seen instructions on how to get rid of the #DIV/0! error on a single cell, but I'm looking for the easiest way to deal with all errors at once in the whole document.

The reason for that is the following: The document was created in LibreOffice, and apparently its behavior is different; instead of an error, LibreOffice displays a blank cell. This problem wasn't identified because all formulas that depend on that result also work (by assuming value 0, I assume). When I open the document in Microsoft Excel 2013, however, any DIV/0! error will cascade down and prevent other formulas that depend on the result to work as well. The problem is that the amount of #DIV/0! errors in the document is way too high to fix them individually.

Example of the content of a problematic cell:

=+Q13/K13

Where Q13 has a fixed value of 12, and K13 is empty.

  • Please share the formula so we can see if we can help you. Just telling us there is a #DIV/0! error doesn't give us much to go on. What research have you done about using LibreOffice files in Excel? – CharlieRB Mar 3 '15 at 17:54
  • How should they be fixed? Should the formula be deleted? Amended? Replaced? Please provide some description of what your goal is. – Excellll Mar 3 '15 at 18:00
  • @CharlieRB I added an example in the question; it's a simple division. As for research about using LO files in Excel, I haven't done much research apart from having worked with the same documents in both without issues, before this one. – Smig Mar 3 '15 at 18:35
  • @Excellll The goal would be to make the document behave like it does in LO, meaning that #DIV/0 errors should be treated as 0 when used in other formulas that depend on that one. The specific way to solve the problem isn't important as long as it can be done for the entire document. I imagine that amending the formulas to return 0 in case of error would probably be ideal; deleting the problematic ones wouldn't be ideal since further work on the document would be difficult, but it would be welcome if it's the best that can be done. – Smig Mar 3 '15 at 18:39
  • How many different cells are directly referenced in such formulas and could potentially be empty? – fixer1234 Mar 3 '15 at 18:44
2

A simple way to change the formula to work with a divide by zero error #DIV/0! is to use the IFERROR function.

In your case, you can do this by using the following formula;

=IFERROR(+Q13/K13, "0")

In this case, Excel will run the formula and if the formula errors, Excel will return a 0 (or leave the 0 out of the quote marks for a blank cell). If there is no error, it returns the results of the calculation.

I don't have LibreOffice, so I can not tell you if this formula will work the other way. You will have to try it.

If the other formulas are dependent on this one, they may begin to work unless this one is 0. You can probably use the Replace feature from the Editing group on the Home tab.

1

I always thought the IF statement in calculations was a bit like cheating. What it does is state that there are special cases where a different value or formula must be shown. I don't find that to be satisfying.

What I do is use the rounding function. Let us say you have a situation to calculate percent of a job done.

A1= How many hours must be done in that day.
A2= How many hours you actually did.

The percent is simply:

=A2/A1

But, what if you don't have any hours to do that day? That means that A1=0. Ooops...instant DIV/0 error.

Yes, you COULD do an IF statement.

IF(A1=0,"0",A2/A1)

BUT, I like to do the following.

=ROUND(A2/(A1+.00000001),2)

What I did there is simply change the value of A1 from 0 to 0.00000001; then I did my calculations then rounded off the insignificant digits. In my case I needed two decimals. Thus my result was "0.00". The size of the insignificant digits you add to your 0 will vary on your particular application. Be aware, that there is a limit to the "smallness" of the number you are allowed to add in excel. I don't know what that value is.

0

http://support.microsoft.com/kb/182189

That shows how to use ISERROR and IFERROR in your formula to replace error messages with something else.

Also talks about using conditional formatting to change the text color to white if it's an error (which just hides the error, but doesn't change it ... I think that's a bit of a half-you-know-what method though. You should really have error-handling built into the formulas.)

However, I could swear I went into "cells" or perhaps excel's main options, and there was the ability to set errors as 0's or blanks or something. I can't remember where. It may require you going into the "cells" menu, then clicking on the format (where you can change to currency, accounting, etc) and making a custom mask.

  • I believe that the option you mention allows you to control weather or not that little green triangle shows up above these type of errors; I don't think it changes the value of the cell in any way. – krowe Mar 3 '15 at 19:46
0

This will convert the selected cell formula into IFERROR() statements which only show valid values (or zero for all errors):

Sub error_fix()
    Dim cell As Range
    For Each cell In Application.Selection.Cells
        cell.Formula = "=IFERROR(" & Mid(cell.Formula, 2) & ",0)"
    Next
End Sub

[ Source ]

Anyway, press Alt + F11 to open the VBA editor and paste the code into it (then save and close the VBA window). Then select the cell that you want to magically work. Then press Alt + F8 to bring up the macro dialog. Select the macro and click Run to fix the selected cell. You could also assign this to a keyboard shortcut by using the Options... button on the Macro dialog.

Be sure to save the worksheet with macros enabled if you want to use this again later.

Update

This fix not only fixes this error but it will fix all errors. That makes it very handy.

I've also updated the script to work properly with selections. Now you can select as many cells as you want and it'll fix them all. You can even hold Ctrl to select multiple cell regions.

  • How would I apply this fix, and what does it do? I'm guessing this is VB, but I'm not well versed in it, nor in using it in Excel. – Smig Mar 3 '15 at 18:41
  • @Smig See the update. – krowe Mar 3 '15 at 19:13
  • I had downvoted your original answer because it only updated one cell. Since the OP said the problem was to find and fix several formulas, that answer was not useful. I've retracted my downvote since your edits to the code. – Excellll Mar 3 '15 at 20:00
  • Well if you're going to downvote someone then you should mention why. Also, even with the single cell version, it is easy enough to assign it to a keyboard shortcut. Not to mention that, even if my answer wasn't complete it was still a good starting point. I'm sorry the code didn't meet your high standards for free advice, ffs. I didn't know we were to be punished for not dotting every i and crossing the t's. – krowe Mar 3 '15 at 20:03
  • This is very nice and handy, but I would suggest that it is arithmetically invalid. The correct answer is to stop trying to divide by zero. Excel raises an error in that case for a reason. – Jamie Hanrahan Mar 3 '15 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.