# Calculation Issue Update

Yesterday we were alerted to an issue in Excel 2007 (and Excel Services 2007) involving calculation of numbers around 65,535.  The Excel team would like to provide a description of the issue and explain what we're doing about it.

Background
Yesterday evening we were alerted to an issue in Excel 2007 (and Excel Services 2007) involving calculation of numbers around 65,535.  The first example that we heard about was =77.1*850, but it became clear from our testing as well as additional reports that this was just one instance where Excel 2007 would return a value of 100,000 instead of 65,535.  The majority of these additional reports were focused on multiplication (ex. =5.1*12850; =10.2*6425; =20.4*3212.5 ), but our testing showed that this really didn't have anything do to with multiplication - it manifested itself with many but not all calculations in Excel that should have resulted in 65,535 (=65535*1 and =16383.75*4 worked for instance).  Further testing showed a similar phenomenon with 65,536 as well.  This issue only exists in Excel 2007, not previous versions.

The Problem
This issue was introduced when we were making changes to the Excel calculation logic in the Office 2007 time frame.  Specifically, Excel incorrectly displays the result of a calculation in 12 very specific cases (outlined below).  The key here is that the issue is actually not in the calculation itself (the result of the calculation stored in Excel’s memory is correct), but only in the result that is shown in the sheet.  Said another way, =850*77.1 will display an incorrect value, but if you then multiply the result by 2, you will get the correct answer (i.e. if A1 contains “=850*77.1”, and A2 contains “=A1*2”, A2 will return the correct answer of 131,070).

So what, specifically, are the values that cause this display problem?  Of the 9.214*10^18 different floating point numbers (floating point on wikipedia) that Excel 2007 can store, there are 6 floating point numbers (using binary representation) between 65534.99999999995 and 65535, and 6 between 65535.99999999995 and 65536 that cause this problem.  You can’t actually enter these numbers into Excel directly (since Excel will round to 15 digits on entry), but any calculation returning one of those results will display this issue if the results of the calculation are displayed in a cell.  All other calculation results are not affected.

The Solution
We take calculation in Excel very seriously and we do everything we can in order to ensure that calculation is correct for all cases.  We’ve come up with a fix for this issue and are in the final phases of a broad test pass in order to ensure that the fix works and doesn’t introduce any additional issues - especially any other calculation issues.  This fix then needs to make its way through our official build lab and onto a download site - which we expect to happen very soon.  We’ll add another post once that’s taken place with a link to the download.

### Office Blogs Comments

• 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.

• "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!