Fix an spreadsheet, Leads to DIV0 all cells

[mohtogh]

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.
MY QUESTIONS:
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.

 

[johnwm]

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
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[bltseattle]

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...

 

[joerd]

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.

Cheers,
Joerd

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

 

[jghrist]

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.

 

[joerd]

jghrist,

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
[tt]=IF(ISERROR(preceding_cell),new_start_value,your_formula)[/tt]
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.

Cheers,
Joerd

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

 

[mohtogh]

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