order formulas are updated..

[danariku]

I have a big workbook with many worksheets, and many formulae in it.
The problem (which occurs whether I have automatic calculation on, or
manual) is that since certain formulas work off other formulas, one of
them is updated before the other, and then the result of one of them is
wrong. e.g. I'll have a lot of DIV/0 errors since one cell referred to
is 0, but if I go into that cell and press enter to evaluate only that
formula (with manual calculation on) then it returns the correct
number. Then I can manually evaluate the division formula and it's OK.

Is there any help for this? Or anything to allow one to specify which
ranges are updated before other ranges?

 

[Niek Otten]

Your formulas should be recalculated in their "natural" sequence. Excel knows which cells depend on which.
However, there are exceptions.
See my standard text on "cells not recalculating correctly". Pay attention to 5. and 6, but don't skip the rest
Please let us know if you had success (or not)

--
Kind regards,

Niek Otten


--
Kind regards,

Niek Otten

=========================================================

Cells not calculating (correctly)

Niek Otten, March 31, 2006



Most frequent causes:



1. Calculation is set to Automatic. This may have happened unintentionally, for example by opening another workbook first.
Calculation is an Excel-wide setting; the first workbook opened determines the calculation mode, which then applies to all open
workbooks and workbooks that are opened later in that instance of Excel. It can be changed manually (again, for all open
workbooks):
Tools>Options>Calculation tab, check Automatic.

2. There are User Defined Functions (UDFs) which access cells directly from within the function, that is, not via the
argument list. Then Excel is not aware of the need of recalculation if the precedent cell changes. You can include
Application.Volatile in the function, but there is no guarantee this will always calculate cells in the correct sequence in all
(future) versions of Excel. It also causes the calling cells (and dependents) to always be recalculated, even if not necessary.
Really the best way is to include all precedent cells in the argument list.

3. You see the formulas in the cell, not the answers. Two options: a. Tools>Options>View, Formulas is checked (you may
accidentally have hit the shortcut key: CTRL+`), b. the cell was formatted as text before you entered the formula. Format as
General and re- enter (F2, ENTER).

4. Excel version 5.0a (yes, very, very old!) has serious recalculation bugs, Excel 97 absolutely needs Service Pack 2 (SP2)
to calculate correctly.

5. Very, very rarely, Excel's dependency tree gets messed up. One way to rebuild it is to find and replace all "=" by "=",
for all sheets. Later versions of Excel rebuild by pressing CTRL+ALT+SHIFT+F9

6. For many calculation secrets, visit Charles William's site:
www.decisionmodels.com

=========================================================



|I have a big workbook with many worksheets, and many formulae in it.
| The problem (which occurs whether I have automatic calculation on, or
| manual) is that since certain formulas work off other formulas, one of
| them is updated before the other, and then the result of one of them is
| wrong. e.g. I'll have a lot of DIV/0 errors since one cell referred to
| is 0, but if I go into that cell and press enter to evaluate only that
| formula (with manual calculation on) then it returns the correct
| number. Then I can manually evaluate the division formula and it's OK.
|
| Is there any help for this? Or anything to allow one to specify which
| ranges are updated before other ranges?
|

 

[danariku]

Thanks for the reply. I tried #5 both ways, and while it seemed
promising as it took longer to calculate after doing them, the same
problem exists. I can go to a cell that has a count of 0, press enter
to calculate it, and it gives me the correct count. Then I go over to
the DIV/0 cell, press enter and it is OK. I am not using any UDFs
either.

The only thing close to a solution I've found is to recalulate the
sheet that was giving me problems individually, that seems to work, but
again it does not work when I recalculate all cells in the workbook.

 

[Niek Otten]

If you really checked all the items in my post and nothing helps, you can mail me your workbook if you like.
Please check once more; others may benefit from it, not from me solving it just for you (if I can!)

--
Kind regards,

Niek Otten

| Thanks for the reply. I tried #5 both ways, and while it seemed
| promising as it took longer to calculate after doing them, the same
| problem exists. I can go to a cell that has a count of 0, press enter
| to calculate it, and it gives me the correct count. Then I go over to
| the DIV/0 cell, press enter and it is OK. I am not using any UDFs
| either.
|
| The only thing close to a solution I've found is to recalulate the
| sheet that was giving me problems individually, that seems to work, but
| again it does not work when I recalculate all cells in the workbook.
|