When Excel incorrectly Calculates

[Illya Teideman]

Has anyone ever seen anything like this, I'd love to get a thorough
explanation of this oddity.. but it does make me question how many times has
excel calculated the wrong result and I've just not noticed!
Workbook called bad.xls
Contains two worksheets "Process Unit Data" and "TAU"
In Process Unit Data in C41 is the formula "=(1210000+599000)/365/0.95"
In TAU in C10 is the formula ='Process Unit Data'!C41
The result in C41 is 5217.02 this is correct
The result in C10 is 3489.55 this is incorrect
This is all the workbook contains
Clicking in the formula bar in C10 and pressing enter results in C10 showing
the correct result of 5217.02 as does saving the workbook with a new name. If
you comapre the bad.xls and the good.xls (i.e once it shows the correct
result) Shows more data in the bad.xls:
Bad.xls (64512 bytes)
Good.xls (63488 bytes)
I've put this down to file corruption... doesn't sound like a major problem
then right... wrong the full spreadsheet (I cut away everything but the two
cells) went out and the error was only picked up by a manual check when the
numbers looked off! How can I trust Excel ever again?! The best bit of this
is stepping through the formula auditing which tels me the correct local
result then puts the wrong number in! I can send you the sheet if you want to
take a look.

 

[Mike]

Try tools - options - calculation and ensure calculation is set to automatic.

Mike

 

[Illya Teideman]

Yes it is and pressing F9 doesn't show the correct result either. The only
things that make it calculate the correct result is clicking in either of the
cells and pressing enter changing the names of the sheets or the workbook or
deleting cells around the affected cells (There is nothing in the cells that
are deleted)... any further ideas?

 

[Jerry W. Lewis]

Has bad.xls been used in multiple versions of Excel? When a workbook that
was saved by a later version of Excel is opened by an earlier version,
formulas are not recalculated.

On the Tau worksheet, what happens if you search/replace "=" with "=" to
effectively re-enter the formula?

Jerry

 

[Earl Kiosterud]

Illya,

Try Ctrl-Alt-F9 (Calculate Full). I spent hours on wrong results in a complicated workbook
that was being populated by macros I was developing, only to find that it was in fact
populating the workbook correctly, but some cross-sheet formulas weren't calculating
correctly. Made me say a LOT of bad words.

 

[Illya Teideman]

Jerry, It was originally created in 2001 so I guess it has been reused and
repurposed continuously since then and may have been used on any amount of
platforms '97 2000 etc it's currently on XP. Is there a Microsoft KB article
for this. Considering the implications of this problem I would hope so!
Making any changes to either formula fixes the issue so yes replacing = with
= does fix the problem... Is the recommended advice to do this on all
spreadsheets that may have been opened or created on older platforms? Am I
alone in thinking this is potentially a big issue?

 

[Illya Teideman]

Thanks very much for that Earl, Yes that works a treat. There have been many
bad words this end as well. . Still a bit worried about the implications of
this issue. I guess I'll just have to let my user base know that they should
press Ctrl-Alt-F9 if they want to make sure the results are correct! ; )
Does doing this once on a sheet and saving it mean that the calculation will
return to automatic or does it need to be calculated like that every time?
Is there any way of fixing this issue on a sheet permenantly to your
knowledge?

 

[Earl Kiosterud]

Illya,

The implications are simple, if serious. You can get wrong answers from Excel. It's a
terrific product, but has too many problems, some that result in incorrect output (now
THAT'S intolerable for a product whose main purpose is to provide such output), and should
have been fixed by now, but aren't, version after version. They've not shown any
inclination to fix even the simple stuff, like the Auto/Manual Calculation switch (Tools -
Options - Calculation) that gets set by the first concurrently opened workbook. There is
the gnarling and gnashing of teeth!

 

[Jerry W. Lewis]

See Help for "About the way Excel recalculates workbooks that were created in
earlier versions", as well as
http://office.microsoft.com/en-us/excel/HP051983221033.aspx touches on it

http://support.microsoft.com/kb/289111
talks about automatic recalculation when you open a workbook in a later
version than what saved it.

Jerry