Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Fix an spreadsheet, Leads to DIV0 all cells

Fix an spreadsheet, Leads to DIV0 all cells

Fix an spreadsheet, Leads to DIV0 all cells

Hi friends,
I'm fixing a big spreadsheet,which includes some iteration.(Actually it is a mass balance table in water treatment plant, including flowrates and concentrations). I want to change some wrong formulas to correct ones, but immidiately after changing formula in a cell, lots of cells convert to DIV0 error.Firstly I solved that problem with changing "calculation" to "manual" mode,change the cells and then calculate (by hitting F9), but again there is such problem in some cells.
1. Is there a general procedure for doing that, without such problem?
2. What is the reason for this problem?
3. Why,initially, I could solve the problem with going to manual calculation?

Sorry for long story, and thanks for your help.

RE: Fix an spreadsheet, Leads to DIV0 all cells

If I'm working on any complex spreadsheet I use the Tools|Formula Auditing toolbar and use Trace Precedents and Trace Dependents before making any changes to a particular cell. That allows me to see the potential effect of changes and gives a better understanding of the overall flow

Good Luck
To get the best from these forums read FAQ731-376 before posting

UK steam enthusiasts: www.essexsteam.co.uk

RE: Fix an spreadsheet, Leads to DIV0 all cells

Based on your description the problem is in your new formulas.  Use the Auditing tool as Johnwm suggests to chaseprecedents.  

Another method you can use, once you find which cell the error originates from, is to highlight each part of the formula in the input box and press F9, which will evaluate the expression that is highlighted.  When you have the error-generating part of the equation highlighted and press F9 you can see the error...

RE: Fix an spreadsheet, Leads to DIV0 all cells

What happens is that if one cell results in a DIV/0 error, all cells that depend on it will also show the DIV/0 error. This makes it hard to see where the error originates. The good news is that Excel can help you with this. If you select one of the cells with the DIV/0 error, and then select Tools/auditing/Trace error, a sequence of red arrows is shown. If you follow this trace backwards, you can see the culprit, and fix the formula.
Generally, there are two methods of preventing division by zero, in the case of the formula =N/D, where D evaluates to 0.
1. Change the formula to =N/(D+0.0000001), which introduces a small error.
2. Test if D=0: =IF(D=0,"error",N/D)
Instead of showing "error" you can probably come up with something more reasonable.


Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Fix an spreadsheet, Leads to DIV0 all cells

I have developed complex spreadsheets that deliberately use circular formulas to do several iterations.  One in particular is a radial load flow that normally converges after a few iterations.  The problem comes when you somehow get a bad value entered that causes a DIV0 error that then propogates to many cells.  Manually calculating usually doesn't help unless you know where the problem originated.  I solved the problem by understanding the calculations and setting up some hard numbers to replace formulas to get rid of the errors.  After a manual calculation to eliminate the errors, I copy the original formulas back.  I have automated this so that a calculations are started by a macro which automatically does the replacements before starting the final calculation.

RE: Fix an spreadsheet, Leads to DIV0 all cells


In some cases you don't even need a macro, since you can test for the error. I think it is more elegant to prevent the error from happening at all, but this is not always possible, in particular with complex worksheets.

So somewhere in the iteration loop, replace your formula (your_formula) with
This  will test if preceding_cell contains an error value, and will then substitute the new_start_value, or else just apply the formula.
Needless to say, you have to replace preceding_cell, new_start_value and your_formula with something sensible.


Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

RE: Fix an spreadsheet, Leads to DIV0 all cells

Thank you guys. You helped me a lot.
I used your methods for finding source of error and finally found it.
Thank you again

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close