Calculation Issue Update

bummer about the excel display idiosyncrasy, but at least i can confirm that there appears to be no problem with Master Chief jumping 65,535 times, nor with 65,536.  found it hard to test 77.1 though ...

Maybe I'm a little bit wrong, but since this works also for using say A1=2^8 -1 and B1=A1+1 I assume it is rather a trouble when moving from smaller numbers to bigger (8bits to 16bits, 16bits to 32 etc.) and has nothing to do with floating point operations. It is probably that the numbers are stored (in memory as well in the file) depending on their "size" to reduce the ammount of memory used. And when they "owerflow" and they are replaced by the next highest value, the "widening" of the ammount of bits fails and uses a badly estimated value (a constant perhaps).

Why doesn't your team stop breaking working code and spend more time on real excel problems like: max row limits and max cell character limits.

Do you really thing it makes my satisfied as a customer to spend time finding tools I've been using for 6 years.

The best phrase in this post:

"We take calculation in Excel very seriously"

God job!

Any function that used the cell with the erroneous display results in an incorrect value.

Rounding issues can be exploited using any digital device that accepts base 10 numbers for calculation, including calculators and "open sores" software (check google, live or yahoo... be creative, you'll find it).

So, please, don't freak out because MS hasn't conquered the problem; it can't be conquered only mitigated. You may lack of familiarity with the conversion issues between base 10 and base 2; thankfully software and chip engineers are on the job, protecting you from the sleepless nights that knowledge of this issue may bring you.

The core issue here is very limited in scope. The likelihood that one of the numbers in question will appear in your sheet is slim at best. Thankfully MS does "take calculation in Excel very seriously". The lack of frequent flames about such issues from "Open Sores" bigots is ample proof of this.

One final rant; Excel is very widely used, and the quality of MS products is highly scrutinized by the public and the media(a good thing). Do you honestly think a competing "Open Sores" product would receive the same level of QA that each major, minor and build version of Excel receives? And if your bank (previous poster) used an "Open Sores" spreadsheet that had a similar issue, do you think you'd ever find out about it; would they?

Maybe users would be better off with OpenOffice?

At least that has had public code review.

Public Function mCorreggi(ByVal s As String) As Double

   Dim d As Double

   d = Evaluate(s)

   mCorreggi = CDbl(CStr(d))

End Function

=mcorreggi("32768/0.09*0.09*2")

@ash65867

That's simply not true. Sounds like you have done little or no testing. Try reading the post agin, and some of the comments, or for a condensed version try this:

veroblog.wordpress.com/.../excel-2007-calculation-bug-displays-apparently-wrong-numbers

(Incidentally, all the things in the article I wrote I have tried out personally, and lots more besides which were not worthy of mention because they behaved as expected. I may have overlooked some possible tests, of course).

Had to correct this because some poor *** is going to believe him and go off and crash a Mars lander someday.

> Floating points are calculated with powers of 2. For instance, 2.5 is :

= ...

+ 4 * 0

+ 2 * 1

The "public code review" or some similar hyperbole is often touted as a strength of "Open Sores" products. Perhaps I'm missing something, but I fail to see how a such a process guarantees superior quality to that produced by the well structured QA processes typically employed by commercial software development teams.

Please help me understand.

"All other calculation results are not affected." ???

FYI: Using the function Average, MIN or MAX on a group of effected cells returns "100000"

@qxq: Master Chief bounces 65,535 times ...

that was very funny  :-)

Maybe if he shot himself in the foot

(like his sponsors) he could do

it 77.1 times.

Based on Erich Neuwirth's posting above on the "twelve magic numbers" which break Excel, I have converted them to IEEE double precision format. Here they are in hexadecimal form:

40efffdf fffffffa

40efffdf fffffffb

40efffdf fffffffc

40efffdf fffffffd

40efffdf fffffffe

40efffdf ffffffff

40efffff fffffffa

40efffff fffffffb

40efffff fffffffc

40efffff fffffffd

40efffff fffffffe

40efffff ffffffff

Definitely an interesting pattern there, although of course not all numbers ending with fffffffX break, only these few.

I'm not the least bit surprised at the calc bug. No non-trivial program is bug-free and I've been teaching people for years not to depend on Excel for certain types of calculation.

Here's a bug that was new to me last week. Excel incorrectly thinks that 1900 was a leap year. That is, if you type Feb 29 1900 into a cell, Excel parses it as a correct date and converts it to a serial number. Well, boys and girls, 1900 was NOT a leap year. If you thought it was, go do your homework. What I understand is that 2000 was, but 1800 and 1900 were not.

Why should we care? Well, perhaps because that makes all dates after Feb 1900 (stored as serial numbers) off by one. So, correcting this seemingly small bug may not be such a trivial thing... think of how many files exist with date info in them!