Fix an spreadsheet, Leads to DIV0 all cells
Fix an spreadsheet, Leads to DIV0 all cells
(OP)
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.
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.





RE: Fix an spreadsheet, Leads to DIV0 all cells
Good Luck
johnwm
________________________________________________________
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
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
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.
RE: Fix an spreadsheet, Leads to DIV0 all cells
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
=IF(ISERROR(preceding_cell),new_start_value,your_formula)
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.
RE: Fix an spreadsheet, Leads to DIV0 all cells
I used your methods for finding source of error and finally found it.
Thank you again