Inconsitent calculations in Excel workbook

[Juanita]

Hi there!

I have a workbook with 480 sheets. About 300 of these sheets have 1
columns and 850 rows of SUMIF statements and the remaining each have 1
columns and 150 rows of links between sheets eg
=sheet1!A1+sheet2!A1+sheet3!A1. Its basically a financial month en
report that HAS to go into a this preformatted Excel workbook.

I'm calculating this on a PC (crazy, I know!). Apart from the fac
that its takes hours to complete, I'm getting consistency problems.
The report doens't balance and when I start to search for the problem
find sheets that just didn't calc. I can find no reason or pattern fo
this problem.

I'm trying to do this on MS Excel 2002 SP3, with a Pentium 4, 3.0 Ghz
512 MB DDR RAM PC

Can anyone help me

 

[Charles Williams]

Try using Ctrl/alt/F9 to trigger a full recalculation.

rehardsCharles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Juanita]

Hi Charles,

The first problem is that it takes too long to recalculate everytim
something goes wrong. Second problem is that everytime there ar
problems on different sheets from the previous calculation. This mean
that I don't really have faith in the calculations..

 

[Charles Williams]

The idea was to check if Ctrl/alt/F9 solved the problem:

What problems do you get?
What makes you think the calculations are inconsistent?
What other formulae and functions are you using apart from SUMIF?

Do you still get the wrong answers after using Ctrl/alt/F9? If so then you
probably either have errors in your formulae, or you have a data problem
such as mixed alphabetic and numeric data so that the SUMIF does not work as
you expect.

If you do get the right answers after Ctrl/alt/F9 then check if the status
bar shows "Calculate" after pressing F9 (possible circular references).

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Juanita]

Hi Charles!

Thank you for your reply. I have extensively tested this workbook.
calculated the entire workbook and got a set of values (wrong ones).
saved and closed it, calculated it again and got a DIFFERENT set o
values (still wrong). I did this 4 times and each time I got differen
values. The 3rd time I got the correct values but the 4th time is wa
wrong again. I did not change any data or any formulas during thes
attempts. In fact, I didn't do any editing within the workbook at all.


So I did the following
- I then went and found a few sheets that I could see didn't calculat
and tried Tools/Options/Calculation/Manual/Calc now on each individua
sheet. The sheet still did not calc.
- Then I tried to F2 in a cell but that didn't calc either.
- Then I copied the formulaes down and it calculated it correctly.
- Then I saved the file and Ctrl/Alt/F9. The exact sheet that I jus
copied the formulas in now did not calculate again!

I am really at a lost

 

[Charles Williams]

check for circular references: you could get exactly the behaviour you
describe if you have them:

Tools-->Options-->Calculation make sure that iteration is NOT checked

press F9, if the status bar shows "calculate" check each sheet individually
for circular references.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm