Calculation errors in an Excel workbook

I have been using Excel 2000 on this Windows 98SE computer for years. An inexplicable problem has developed. I have a table in which, typically, SUM(B1:B10) is in the cell at the bottom of each list. The sum, however, is incorrect in many cases. If I refresh that instruction, the values in a number of cells change for some reason. I used this type of table setup in another sheet year without any problem. I have requested Help on this issue without any response to date. I know that I must have made a mistake but I have not been able to reason what it is.

 

cells B1 to B10 and the other column lists all contain formulas. Each list has only one value obtained by an IF formula searching other cells. The SUM should be the same as the list value but they differ mostly by a small amount. Pressing F9 changes both sets of values but the error remains. Pressing F9 changes the values in a different manner each time.Options>Calculation was set to Automatic except tables. I changed it to Automatic but that made no difference. The problem is on Sheet 4. Sheet 3 contains nominally the same table as on Sheet 4 The SUMS and values are the same on Sheet 3, as they should be, and pressing F9 does not cause any change.

 

When I booted today and opened the Excel, the SUM and cell values were the same. When I pressed F9, no values changed.
In this sheet, there are a number of cells for input values and a large number of cells where formula calculate values downstream from the input values. The calculations include the values in the lists mentioned and the associated SUM values.
I then input a value and many calculated values changed. However, some of the list and Sum values were different again. I pressed F9 again and many values changed. This happened repeatedly, then stopped. I cannot get repeated results for the input values.

 

I have now noticed that when the list values and SUM values change on clicking on F9, the new SUM values are the previous list values.

 

It seems that the SUM formula working on previously calculated cell values indicates that the EXCEL on this computer is corrupted in some way. Is there a site I can go to that advises on such matters?

 

I have now checked operations in other sheets of this workbook. Changing input values are immediately reflected in the changed calculated values. Pressing F9 does not change the calculated values.That is, normal operation. However, all sets of operations in sheet 4 (the faulty one) show the same fault, calculations for a new input value require repeated pressings of F9. The SUM failure is just a symptom of the fault. It is like Calculation is set to Manual but Automatic is the setting in the Options box.

 

Hi Denisaf2000, Have you tried the Detect and Repair?
My MS Office 2003 has this facility > Open Excel > Help > Detect and Repair.
You may have to have your original software source available as you may be asked for it during the operation. Cheers Neil.

 

Hi Neil
I have Excel2000. The Detect and Repair on Help would not work. The advice was to reinstall Office. I did this and it went through the repair procedure. I then input a value into the spreadsheet. It did not do the calculation automatically. I had to press F9 repeatedly to get the answer. I checked that the Option>Calculation was on Automatic. I thank you for that suggestion as it reminded me of steps to be checked. But I am a loss as to what to do next.

 

Have a look st this:http://www.mrexcel.com/forum/showthread.php?t=38733
This might be the answer to your problem. Neil.

http://answers.yahoo.com/question/index?qid=1006022409806

http://www.tek-tips.com/viewthread.cfm?qid=771235&page=1166

Don't know whether these will help but could be worth a look.

 

I had previously posted to mrexcel without a positive result. I have examined the mrexcel thread mentioned but that just provides information of what people have tried without resolving the problem. Some of the sections of the problem sheet calculate immediately while others do the recalculation only by repeated F9. I am trying to find if there is a link between the faulty sections. It that does not work, I might decide to go back to answers and be prepared to pay their price if they do come up with an answer!

 

Sorry I couldn't help further but I only played with Excel about 6 years ago and have no real needs at the moment. I hope you get it sorted and that someone more knowledgeable in Excel will look in here. Cheers Neil.

 

Thanks Neil for your input. I have run a few more checks. Most of the calculation sections work. The only ones that need the repeated F9 involve hundreds of calculations. The number increases each week. The problem developed only two weeks ago so it is possible that there is a limit to the number of calculations done automatically and each F9 causes another batch to be done until they have all been recalculated. I have not been able to find reference to such a limit.

 

I have found that the failure to recalculate in my sheet relates only to those sections involving many calculations. I needed to repeatedly press F9 to get a result for a new input value. I noted that a recalculation should take place automatically when the sheet was saved. I tried both just saving the sheet and closing/opening. Neither worked. I noticed that a partial calculation occurred because I saw values change as after I clicked on SAVE or EXIT. However, I still had to repeatedly press on F9 to get the final result (which I now know for each input value)

 

Have you tried the suggestion in Post #6 in Mrexcel link?
The idea was that too large volume in the sheet caused that problem and George saved to another folder which enabled the calc to work.
If you consider this Thread is Resolved - would you do the honours and mark it as such. LOL. Neil.

 

I have posted the sheet to another workbook and that did not help. I understand there are limits to the number of calculations that are carried out automatically. I had hoped that these limits could be changed and someone could advise me on how to do that. The matter is not resolved as far as I am concerned but I will mark it off if I am advised that these limits cannot be changed.