Microsoft Excel

PingBack from http://www.artofbam.com/wordpress/?p=2948

David, your explanation of the source of the problem is not clear. You are emphasizing that Excel is performing the calculation properly, but not explaining what component of Excel that is causing the results to be displayed incorrectly.

If there is a problem in the presentation or data translation layer of Excel then elaborate on those aspects, as your current explanation gets murky with all the discussions of floating point math and floating point value representation.

Your explanation won't make sense to any top business executive who relies on Excel in their organization, and can be swayed by positive press from competing products.

Due to the PR issue this has caused your company will need to advertise the download and emphasize the importance to users to ensure they download the update. Otherwise this issue will resurface infrequently, generating more bad press.

You should also review your quality assurance testing process over the presentation portion of Excel. Perhaps it is time to reorganize the policies and procedures of the responsible QA team.

So what, specifically, are the values that cause this display problem? Of the 9.214*10^18 different floating point numbers (floating point...

Thanks for the fast response and the open explanation David.

Would I be right in thinking part of how this got through QA is that its a presentation issue rather than an underlying value thing, so any value-based test will pass?

drewbc - sorry, I was trying to walk the line between not too much detail and too much detail.

drewbc and simon - this is an issue in a function that puts numbers in cells, so the values in Excel's memory are actually correct.  Imagine A1 contains =77.1*850 ... Excel actually calculates the correct answer, and you can see that if you use VBA to check the value for A1 - it will be 65535.  But in the function that takes that value and formats it to be displayed on the screen, for the values described above, there is a bug.  Any calculations based off that cell will be accurate too.  Hope that helps.

This same calculation issue affects Excel Services, and is being addressed concurrently. http://blogs.msdn.com/excel/archive/2007/09/25/calculation-issue-update.asp

David,

You said "Any calculations based off that cell will be accurate too.  Hope that helps."

But that's not entirely correct. At least not from what I have seen. If you happen to be rounding your calculations (=ROUND(850*77.1,2)*2), it also rounds it to 100K making that permanent.

So, while it is true that most cases Excel treats the value as correct except for the visual side of it, in others it actually DOES treat it as 100K.

Rob

Dave, thats what I thought - I've some VBA code on my blog to test the .text and .value2 of formula cells looking for this issue. some numberformats break it though.

Gisteren had ik er al over ge3schreven en nu komt het Excel team met een reactie: Yesterday we were...

"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)."

This is not always correct. In your example, put =A1+1 in cell A2. It will not return 65,536, but rather 100,001. If you put the calculatoin in a table (just a row and columns of numbers, not an Excel "Table" and then put a filter on it, the fitler will see those erronious values as 100,000, not 65,535.

A1 =850*77.1   --> 100000

B1 =A1+1       --> 100001 (realy a display bug???)

C1 =A1-1       --> 65534

D1 =B1-C1      --> 2

About the ROUND() and A1+1 - didn't it occur to any of you, that these operations actually produce another one of the "bad" values? ;)

Try A1+2 or A1+3.

Nope. It's not a "display bug".

"+1" gives 100001

"-1" gives 65534

Just to add to the oddity...

12*5461,33333333333 = 100001 :-)

The test "D1 =B1-C1      --> 2" above is consistent with a display bug  since C1 = 65534

so B1 must be equal to 65536 (which, due to the display bug) according to the posted, is shown as

100001

If calculation is right and only display is the problem, is there a way to show the result in text, for example "three four decimal nine nine nine nine nine"????

aha, das problem ist ja schon richtig

bekannt und fast erkannt. das problem ist

nicht die rechenoperation sondern das ergebnis

als solches.

Hi,

strange thing - not only in Excel.

If I paste the following in Visual Studio 2005:

Dim x

x = 850 * 77.1

and execute it, the result is

Double: 65534.999999999993

rather then 65535

Interessant, dass das Problem genau an der 16-Bit-Grenze auftritt: als beim Übergang von 2^16-1 = bin. "1111111111111111" zu 2^16 = bin. "10000000000000000".

Schließlich hatte die letzte gültige Zeile in Excel 2000 (und Excel 2003) auch die Nummer 65636 (= 2^16).

Regarding x = 850 * 77.1 // 65534.999999999993

This seems link normal floating point rounding, and present in all floating point math.

Re:

"Dim x

x = 850 * 77.1

and execute it, the result is

Double: 65534.999999999993"

Now, that is not unexpected.

You can't really convert 77.1 into binary *precisely* (it's a periodical binary fraction), so there will be a rounding error. Which will be increased when multiplying by 850.

Normally when displaying, you cut off the last digit or two while rounding the last remaining, to not show the error, and it gives an illusion of integer numbers as results...

Will this fix be pushed via Windows Update? Or will explicit download and installation be required?

VilX,

This is not the case. Any calculation made with a direct result of the 65535 results in a correctly displayed value. So, this was David's original point.

However, that breaks when you add "external" elements to the calculation such as rounding. In this case, the display value of 100K that behaves like 65535 except for the visual part, now effectively becomes 100K. THAT, to me, is much more dangerous because if you end up in a transition number that you have rounded, it will become 100K and everything else will be based on this new number!

Rob

It will be pushed via Microsoft Update, which is part of Windows Update for Microsoft Office users.

I don't see how 850 x (the rounding error you are describing) = (100,000 - 655534.xxxxx)

Gli scherzi di Excel 2007

To me the idea that the value in memory is correct (for most instances where I see Excel used) is meaningless.  It's normal users who are making determinations from what they see in the cell, and what they see is wrong.  Most of the users I work with do not know how to get to the actual value in memory and would look at me as if I had two heads if I made that argument to them.  The number is wrong and that is all there is to it.

You say that only the "graphical" representation is wrong. You say that the number is internally correct.

Try this

A:1 cell value 850

B:1 cell value 77.1

C:1 cell formula =A:1*B:1 gives 100000

D:1 cell formula =C:1+1 gives 100001

E:1 cell formula =A:1*B:1+1 gives 100001

PLUS the excel is so arrogant as to point out that E:1 formula is "inconsistent"

Above is consistent with a display issue once you realise 65535 is displayed as 100000 and 65536 is displayed as 100001

As others have suggested, add +2 or +3

Question: When does 850 * 77.1 not equal 65,535? Answer: When you're using Excel 2007. A bug in Excel

Also do not forget that many people copy and paste values into another sheet for batch processing in many financial institutions.

This error is carried over in those cases. and will post as 100,000 in a CSV file as well. so it is NOT a display error only.

I think the key of this bug maybe the number 17 and at least one decimal ended without "5".

the detail description was written in Chinese here:

http://www.sucirst.com/index.php/news/36

"Question: When does 850 * 77.1 not equal 65,535? Answer: When you're using Excel 2007. A bug in Excel."

Answer: When you are dealing with infinite binary fractions, 77.1 can only be approximated. So multiplication just increases this error. Normally you don't see it because last 1-2 digits are thrown off and the rest is rounded and you see precise result...

But the bug is in Excel all right.

hooray!

I thought i'd spent my Google Adwords budget for the month but i've actually got $34,465 left!

Hi folks, thanks for all the comments.

Couple of points.  First, I want to reiterate that we take calculation in Excel very seriously and we are working hard to produce a fix.  I made the distinction between display and calculated value in an effort to explain the bug to the community, not to suggest the bug was less severe.

Second, the reason you need to add 2 in my example above is because adding one just gets you from a value near 65534.99999999995 to a value near 65535.99999999995, which is still problematic.

Third, we will let everyone know how to get the fix as soon as it is available.

Thanks for the update, Dave.  Now that we've beaten this issue to death, hopefully we can move on to other things and I can de-clutter my Inbox of all the automated messages this darn discussion has generated :-)

I can produce 6 of the 12 numbers causing the problem. 65535-2^(-35), 65535-2^(-36), 65535-2^(-37)

and 65536-2^(-35), 65536-(2^-36), 65536-2^(-37).

What are the other 6 numbers?

The problem with these numbers is that

MOD(...,1) for any of these numbers produces

-2^(-37) or the other values subtracted.

MOD(...,1) never should produce negative values.

ROUND(..,0) for any of these numbers produces the wrong value, 100000 or 10001. INT produces the correct value. So if the display engine internally used ROUND and ROUND depended on MOD,

that would be an explanation.

Other strange bug:

Enter in a cell

=4.1-2*2

Show the cell with at least 16 numbers after the decimal separator and it seems that 0.1 is not really 0.1

Tried this last bug in OpenOffice too. There also 0.1 is not really 0.1 Though when the calculation is =0.2-0.1 the answer is 0.1

But 850*77.1 is 65535 in OpenOffice.

=4.1-2*2 gives me 0.09999999999999960000 which would be wrong.

If Excel follows the order of opperations the equation should work out to: (4.1-(2*2)) which would give you (4.1-(4)) which is 0.1 NOT .09999999999999960!

This is with Excel 2004 for the Mac Version 11.3.7 (070724).

Where is the bug in that? Is Excel 'approximating' answers?

Weird. If I put "=4.1-2*2 in a1 and then in b1 put "a1-0.1" I get .0000000000000004!

Somewhere in the multiplication routines in Excel (for the Mac) there is an issue with precision and it just doesn't make much sense.

I guess that for really big, or really small numbers you shouldn't rely on Excel or if you push the precision to greater limits.

Is it sloppy programming or what?

The last few posters referring to

"=4.1-2*2 gives me 0.09999999999999960000" should read the prior postings.  In floating point maths, this is acceptable since all floating point maths has some v. small rounding when converting from decimal to binary representation.  This is seen in all Excel versions, and is accepted globally.  

This could be just picking nits because how many times do people run precision out over 14 places in 'average jobs'?

This isn't like the 'Great Pentium Bug' that blew up out of proportion.

Here I'm speaking about the issue of the -4.1-2*2.

But the bug isn't in the multiplication routines though, on looking back because 2*2 does equal 4.000... AND 4.1 does equal 4.1000...

So how does 4.1-4 equal 0.09999999999999999600?

You don't even need to throw in the multiplication to get the wrong answer at high precision. Just do =4.1-4!

This is seen in all Excel versions, and is accepted globally.

Ok. Thank god that Microsoft warns its users not to use the results on life or death decisions.

The floating point answer doesnt quite wash though.  The first example of 850*77.1 gives a clean 65535 answer, there is no rounding!  The 16383.75*4 example also gives a "clean" 65535 answer and displays it correctly.

God! We've gone from a calculation bug to floating point precision limits that's been there since time begun and *isn't* an Excel only issue.  When will this madness end!

If you really don't believe it.  Drop this into a module and do a =TrueValue(cell) to see.

Public Function TrueValue(cell As Range)

   TrueValue = "" & cell.Value

End Function

And Karen, 850*77.1 gives a clean 65535 without any rounding IN DECIMAL.  Computers use binary to calculate numbers, not decimal.

I think that I found a related bug which shows up in both Excel 2003 and 2007.  Namely, if you key in the following:

=DEC2HEX(2^16), it correctly gives 10000 (hex)

=DEC2HEX(2^16-1), it correctly gives FFFF (hex)

=DEC2HEX(65535), it correctly gives FFFF (hex)

=DEC2HEX(850*77.1), it incorrectly gives FFFE (hex)

Good Lord people! Floating point operations are by their very nature innacurate, period. It's always been this way, doesn't matter if we're talking PC, Macintosh, mainframe, nor Windows/Mac/Unix/whatever. Anyone who truly understands computers accepts this as an axiom. People who only know how to use Excel (i.e. the general public) are no doubt horrified by these recent "revalations", but computer science types are laughing at the ignorance floating about (pardon the pun)

See http://en.wikipedia.org/wiki/Floating_point for everything you never wanted to know about floating point representation.

Actually check out this Wiki section in particular:

http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems

From Slashdot we learn that Microsoft Excel 2007 has a serious multiplication bug. Drop “=850*77.1” into a cell and watch it turn into 100,000 instead of 65,535. In fact, a lot of the results that end up all 1's in...

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

= ...

+ 16 * 0

+ 8 * 0

+ 4 * 0

+ 2 * 1 <- here's the 2...

+ 1 * 0

+ 1/2 * 1 <- and here's the .5

+ 1/4 * 0

+ 1/8 * 0

+ 1/16 * 0

+ 1/32 * 0

...etc.

In short, you add powers of two, either above 1 (for the integer part), either below (for the floating part). This is the shortest and fastest way to encode a wide range of numbers in a short memory space.

Some values cannot be coded exactly this way, like 0.1, 1/3 or Pi. 0.1 is coded 1.0000000000001 instead (or 0.999999....). But in math, 1.0000000000001 is a strict equivalent to 0.1 (and so is 0.9999999...), so it's neither an error nor a bug. It's just how binary calculations work.

And that's how 4.1-4 equals 0.09999999999999999600. It's by design. And if you round it up (which you ARE supposed to do if you know what you are doing anyway), the result will be 100.000000001% correct. ;)

Joel Spolsky has a detailed explanation of the bug and what might have caused it on his blog: http://www.joelonsoftware.com/items/2007/09/26b.html

via MSDN Excel Team blog Excel 2007を持ってな...

[display result = 100000]

=850*77.1

=1700*38.55

=19.275*850*4

[display result = 65535]

=13107*5

=655350*0.1

=4250*15.42

=3.855*4250*4

=15.42*4250

=1062.5*15.42*4

[Introspection]

Finding someone else's bug is easier than finding our own (software) bug. It's just a bug, not an elephant :)

Ha! Just try in OpenOffice 2.0, there is NO this bug.

Lets send Excel and other M$ products to garbage.

Viva Open Source!

When you issue a fix, it might be useful if you also would allow us the option of changing keyboard commands. For example, I'd like to make F1 equal to save the file....

This has evidently been confirmed by Microsoft, and there is a fix on the way. For more on this, check

Hmmm, it's not too long that I had to go through masses of OOXML documentation in which compliant applications had to replicate bugs and oddities present in older applications for "backwards compatibility" reasons.

Will applications also have to replicate this behaviour to be OOXML-compliant? :D

if you use ROUND, the error becomes easier to be propagated to other cells:

=ROUND(77.1*850,0)

Now, if you refer this cell (e.g.: A1*2), you will work with the 100k: 200,000

i think for business people who rely on excel, the technical explanation will not help them. They only want to know that their calculation in excel is correct, and this bug is a really disaster for excel's great images.

Hope this stupid bugs will not come in your future product.

In questi giorni sta rimbalzando in rete la notizia di un bug alquanto preoccupante che affligge Excel

While you are doing bugfixes, how about the fact that in office its impossible to sort out

10W10

10V20

10V10

10W20

list correctly... dont have 2007 here but 2003 gives

10V10

10W10

10V20

10W20

I hear thats now fixed in the file explorer, when can the office ppl do the same ?

Sarà difficile spiegarlo a chi non è tecnico, ma è un dato di fatto che l&#39;unico programma senza bug

As the related wikipedia article states...

"Floating point arithmetic is at its best when it is simply being used to measure real-world quantities over a wide range of scales (such as the orbital period of Io or the mass of the proton), and at its worst when it is expected to model the interactions of quantities expressed as decimal strings that are expected to be exact. An example of the latter case is financial calculations. **For this reason, financial software tends not to use a binary floating-point number representation.**"

:)

Mathematically, 850*77.1=65535. When looking inside the OOXML file, the value is not correctly recorded, but 65534,9999999994321564 or whatever. How will this "rounding", obviously wrong, propagate further ? Maybe it's time for MS programmers to start a riot against their chief, shouting "we want to build great softwares, we want unitary tests, we want code auditing, we don't want to program buggy and low quality software for marketing or whatever". Better the best of them should leave slapping the door to go to the "good software" world of FLOSS, where softwares are build for the beauty of art, the satisfaction of users, the love of nice work, the progress of the IT world. The MS train is running too fast, too heavy, too complicated and is about to run out of the rails with major casualities. Time to switch.

@Pinky

The Great Pentium Bug as you call it was a real bug. Part of the table used for floating point divisions was missing some values IIRC, and it wasn't that difficult to trigger either.

It just took Intel some time to admit that it was indeed a bug in the CPU and that they had to replace the CPU's to fix it.

"We take calculation in Excel very seriously"

Oh My God !!!

"This issue only exists in Excel 2007, not previous versions"

So are you trying to fix something that ain't broke?

I found 12 numbers creating the problem now. If the statements above by David Grainer are correct, these are all the faulty numbers. They are

655535^-2^(-35)

655535^-2^(-36)

655535^-2^(-37)

655535^-2^(-35)-2^(-36)

655535^-2^(-36)-2^(-37)

655535^-2^(-36)-2^(-37)

655536^-2^(-35)

655536^-2^(-36)

655536^-2^(-37)

655536^-2^(-35)-2^(-36)

655536^-2^(-36)-2^(-37)

655536^-2^(-36)-2^(-37)

You cannot type them int a a cell as a value,

But you can create them with a formula

"All other calculation results are not affected."

Since you say this with such confidence you must either have tested all other number, or you have a formal proof of this statement.

Congratulations!  Both those feats are quite impressive.

Here's the quick test i made yesterday :

Put 65535 on a row ...

Divide it by 1 then 1.1 then 1.2 etc etc

Multiply it back by 1 1.1 1.2 etc etc

Round it so to know how many "display bug" we have

Go up to say 10000 for ex ....

65535 998 65,66633267 65535 65535 0

65535 998,1 65,65975353 65535 65535 0

65535 998,2 65,65317572 65535 65535 0

65535 998,3 65,64659922 65535 65535 0

65535 998,4 65,64002404 65535 65535 0

65535 998,5 65,63345018 100000 100000 1

65535 998,6 65,62687763 100000 100000 1

65535 998,7 65,6203064 65535 65535 0

65535 998,8 65,61373648 65535 65535 0

65535 998,9 65,60716788 65535 65535 0

65535 999 65,6006006 100000 100000 1

65535 999,1 65,59403463 65535 65535 0

65535 999,2 65,58746998 65535 65535 0

65535 999,3 65,58090663 65535 65535 0

65535 999,4 65,57434461 100000 100000 1

65535 999,5 65,56778389 65535 65535 0

65535 999,6 65,56122449 65535 65535 0

65535 999,7 65,5546664 65535 65535 0

65535 999,8 65,54810962 65535 65535 0

65535 999,9 65,54155416 65535 65535 0

65535 1000 65,535 65535 65535 0

1436

I get to the "Magic" 6 65534.99999999995 to 65535 numbers more than 14% of the time ?????

Oh well ....

Back to 2003 until the patch ,(

Let's Rock GUYS!!!!

Give us a fix and we will be happy!

Any bad word... any worry... any... any....

don't think about it...

Work hard...

give us the fix...

And thanks for your work! : )))

Ale from Milano

: )

That's why most of us are staying with Office 2000 and Windows XP.

Remember my boss was so mad when his wireless keyboard missed a key stroke.  One hundred thousand became ten thousand could be a very serious mistake.

If you send out an Excel order form to order 850 pieces of $77.1 parts.  The recipient open it with Excel 2007 and charge you $100,000.  This is no fun.

A1 850

A2 77,1

A3 =(A1*10)*A2/10

Result 65536

A4 =(A1*10)*(A2/10)

Result 100000

Maybe this helps you.

Bye

guys, these is serious matter for us as power users of excel next year. When is solution coming?

Ayer publicábamos un serio problema detectado en Microsoft Excel 97, que multiplicaba de manera incorrecta algunos números. En concreto el problema reside en el número 65.535, una cifra que cuando es obtenida como resultado de una multiplicación produ

Assuming B1 as the bad calculation, we got this:

  A       B       C       D       E

1  65534   100000  100001  65537   65538

-> B1-1            B1+1    C1+1    D1+1

Ok but if my bank used excel to calcule something about my money and I lost $ can I ask a refund to Microsoft??????? It´s absolutely uncorrect!! Fight Microsoft!!

If your bank is using EXCEL 2007 to calculate something about your money... I suggest you to change bank! : )))))

Microsoft explained the bug as a display problem. Is it true? Unfortunately, it's definately a calculation problem.

Try this:

A1=round(850*77.1,1)

then all the calculation error with the A1 will be permanent. It's very serious problem, e.g.

A2=A1+2, as they explained the 850*77.1+2 should be 65537.

ROUND and "set precision as displayed" will both give rise to an actual incorrect value.

I guess the code for both are predicated on the same algorithm for converting from the floating point format.

ROUNDUP and ROUNDDOWN seem unaffected, though

http://veroblog.wordpress.com/2007/09/26/excel-2007-calculation-bug-displays-apparently-wrong-numbers/

same URL made tiny: http://tinyurl.com/3xweb5

"If your bank is using EXCEL 2007 to calculate something about your money... I suggest you to change bank! : )))))"

Since MS is forcing everyone to switch to 2007 by ceasing support for older versions of office in the coming year(s), all major corporations are having to plan and test for the switch this year. Meaning that there are many branches in testing now.

If you don't think that every bank branch in America is using Excel at their desks to look at data, process data, prepare the files for uploading to batch process overnight, Your are in a dreamland and I really hope you never realize how deeply MS is embedded in the financial institutions, you might have a heart attack.

Enquanto eu puder piratear o Excel, vocês podem demorar o tempo que quiserem pra liberar o patch....

Any chance to know when the fix will be released?

I'm not looking for precise date, but are we talking about a few days or weeks?

and if you try with OpenOffice?

OOo, iWork Numbers, and a few others that I have heard tested in the past few days are fine.

this is specific to MS Excel.

With all the talk of floating point accuracy and the seriousness with which Excel takes it have a glance at these reports that show Gnumeric as a spreadsheet of choice if you take accuracy seriously.

http://www.csdassn.org/software_reports.cfm

I don't know if they have been updated for the latest Excel.

I've found the fix to the Excel bug.  You can download it here:

http://download.openoffice.org/2.3.0/index.html

I was looking at some of the reaction to that "bed-wetter" article we saw in yesterday's Clicked and saw an unexpected theme: defining maturity. I'm thinking the cultural context for the question is that we typically think of diplomacy and negotiation

To David Gainer:

Can you comment on the posting by Brian H. on September 26, 6:58 AM, that I have appended, in which he indicates there is instance of where the display representation seemed incorrect, for a value much different than 65535 or 65536? In particular, he indicates that he gets:

12*5461,33333333333 = 100001

Thanks and regards.

Can posters please exercise some logical checking

12*5461.33333333333  = 65535.99996  -> i.e. 65536

The poster was probably in Europe when a "," is used for a "."  .. or simply a typo of the poster.

JPDeyst : before asking for David to reply, did you check if you get the same result ?

I'm curious about how this bug was generated. I am baffled because .NET applications can perform this calculation perfectly.

Unless you used VB.net instead of C#

Anonymous:

Thanks for the explanation. Now I get it; Brian H. was having some fun at the expense of those of us not well versed in using comma delimiters.

No I don't have Excel 2007 at my work place.

-JPDeyst

Acabo de aterrizar de Ibiza de unas minivacaciones impresionantes (y en las cuales de paso he escrito

So if you just mutiple eveything by 1 it will work...

what is so wrong with that?  

I think that I have found a second related bug that is in both the 2003 version of Excel and the 2007 version.  Namely:

If you key in the following:

=DEC2HEX(2^16), it correctly gives 10000 (hex)

=DEC2HEX(2^16-1), it correctly gives FFFF (hex)

=DEC2HEX(65535), it correctly gives FFFF (hex)

=DEC2HEX(850*77.1), it incorrectly gives FFFE (hex)

Hi,

I have created Excel Addin in 2003. I have converted it to Excel 2007 Addin , but it contain more no of rows and columns as compared to excel 2007. I would like to preserve same no of rows and columns for Addin in Excel 2007. Is there any solution for it ???

Please reply.

Thanks & regards

-Mangesh

Hi, Few changes in first comment.

I have created Excel Addin in 2003. I have converted it to Excel 2007 Addin , but it contain more no of rows and columns as compared to excel 2003. I would like to preserve same no of rows and columns for Addin in Excel 2007. Is there any solution for it ???

Please reply.

Thanks & regards

-Mangesh

Isn’t it a coincidence that 65,536 is the number of rows available in Excel 2003.  Conversion issue?

@Scooter

Nothing to do with it at all. What would conversion have to do with anything anyway? The bug appears on any new spreadsheet in native Excel 2007.

It just happens that 65,536 = 2^16, so it is the largest numnber which can be represented in two bytes, and seemed a reasonable way to save file space by not wasting lots of memory remmembering low row numbers when the large ones may never have been used.

It was a reasonable limit for Excel given the power of the machines available at the time to calculate large sheets (arguably the move to over 1M rows is now arriving a few years later than it could have done).

Check your graphics card settings - do you have choices for 256 colours, then 65,536, then 16.1 Million? Does that 65,536 have anything to do with this Excel bug? Clearly not, this is simply 2^8, 2^16, 2^24 expressed in decimal.

@wlandrum : You say

"=DEC2HEX(850*77.1), it incorrectly gives FFFE (hex)"

850*77.1 returns 65534.9999999994321564 (which is acceptable for floating point math as seen in prior postings in this thread) -- so DEC2HEX( ) seems to "round down", and thus gives the CORRECT answer FFFE (=65534).

while 65534.9999999994321564 is a PERFECTLY acceptable value for 850*77.1, FFFE is in no way acceptable for  DEC2HEX(850*77.1). It is plain WRONG. No matter how you look at the problem.

(BTW. OpenOffice properly displays FFFF for this)

Here 's an official update on the issue.

@mihaimm :

Since you agree "65534.9999999994321564" is a PERFECTLY acceptable value for 850*77.1"

then what would you expect

DEC2HEX(65534.9999999994321564) to return if it is defined as rounding down ?

Surely FFFE.  Therefore DEC2HEX(850*77.1) must return FFFE

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:

http://veroblog.wordpress.com/2007/09/26/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 schmuck 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 <- here's the 2...

+ 1 * 0

+ 1/2 * 1 <- and here's the .5

Sorry, Charlie.  Time to go back to school. :)

This is a correct representation of the binary value of 2.5 in "fixed point" representation, NOT "floating point".  "Floating point" means that the location of the decimal point becomes irrelevant to the representation of the number.  What is stored is the (value of the) sequence of significant digits, called the "mantissa", followed by an exponent value which indicates where the decimal point goes.

Wikipedia has a decent article on this, but it's clearer at http://steve.hollasch.net/cgindex/coding/ieeefloat.html.

Happy computing...

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!

Just an interesting observation.

The last row number in an Excel Spreadsheet  (looking at Office XP, at least) is row number 65535.  

Just an interesting observation (correction).

The last row number in an Excel Spreadsheet  (looking at Office XP, at least) is row number 65536.  

George Mack - doesn't that only matter if you are referencing things prior to that date? I should think that the vast majority of modern spreadsheet use does not utilize dated data from 1900. Besides, I've always worked under the assumption that time didn't really exist prior to 1971, so I've just ignored it. :)

I guess any discussion of a bug in Excel that goes on long enough is bound to bring up the 2/29/1900 feature. For those who do not know: this was done on purpose for backward compatibility with older non-Microsoft software.

David, maybe it was just infortuitous timing, but this trollfest makes me sorry I suggested leaving comments open.

That's interesting, I haven't upgraded cause I don't use it too often, and have Excel 97 sr2.  I tried a number of these examples, and all of them resulted in 65535, I'm seeing this error in Excel 97 sr2.

It's fun to see such smart people and such ignorant people discuss things!  Kind of like science vs. religion stuff... and elephants vs. donkeys.  Please can anyone tell me what 2^16 is?

The "problem number" 65536 happens to be the maximum number of rows in an Excel spreadsheet. Somewhere in the Excel code, there's obviously a procedure that tests for values between 65535 and 65536, to trap out of bounds errors. A problem might arise if that subroutine is being unnecessarily called to evaluate formula results.

OK here's another crazy Excel error:

A       B         C

=A2-B2

4.1 4 0.09999999999999960000

5.1 5 0.09999999999999960000

6.1 6 0.09999999999999960000

7.1 7 0.09999999999999960000

8.1 8 0.09999999999999960000

9.1 9 0.09999999999999960000

10.1 10 0.09999999999999960000

11.1 11 0.09999999999999960000

12.1 12 0.09999999999999960000

13.1 13 0.09999999999999960000

14.1 14 0.09999999999999960000

15.1 15 0.09999999999999960000

16.1 16 0.10000000000000100000

17.1 17 0.10000000000000100000

18.1 18 0.10000000000000100000

19.1 19 0.10000000000000100000

20.1 20 0.10000000000000100000

21.1 21 0.10000000000000100000

...

I found this in Excel 2003. You will nead to 'increase decimal' to see the significant figures. I would be interested to know if any one else has found the same errors. My guess is in how excel stores the numbers, as in a short, int, float, long, or double. And the loss of precision in the calculation.

This does NOT appear to be just a display problem. when I then used those numbers in other formulas the strangeness continued. Try =B2*C2 to see what I mean.

also:

=A2-B2

4.1 3 1.10000000000000000000

5.1 4 1.10000000000000000000

6.1 5 1.10000000000000000000

7.1 6 1.10000000000000000000

8.1 7 1.10000000000000000000

9.1 8 1.10000000000000000000

10.1 9 1.10000000000000000000

11.1 10 1.10000000000000000000

12.1 11 1.10000000000000000000

...

all come out correct. Interesting...  

The previous post didn't format the "columns" correctly.

in Excel

A     B       C

4.1    4      =A2-B2

5.1    5      =A3-B3

...

autofill in those downward to see what I mean.

Thanks

OK I just found another level of strangeness.

the pattern of error changes at every 4^n.

4.1

16.1

64.1

256.1

1024.1

4096.1

A         B          C

                      =A2-B2

1.1   1   0.10000000000000000000

2.1   2   0.10000000000000000000

3.1   3   0.10000000000000000000

4.1   4   0.09999999999999960000

...

15.1   15   0.09999999999999960000

16.1   16   0.10000000000000100000

...

63.1   63   0.10000000000000100000

64.1   64   0.09999999999999430000

...

255.1   255   0.09999999999999430000

256.1   256   0.10000000000002300000

...

1023.1   1023   0.10000000000002300000

1024.1   1024   0.09999999999990910000

...

4095.1   4095   0.09999999999990910000

4096.1   4096   0.10000000000036400000

I didn't go beyond 4096.1, but I think I can see a pattern here...

Leap years cause almost as many problems as floating point calculations.  This one is not an "error" in Excel, so much as a historical error for compatibility purposes.

Lotus 1-2-3 incorrectly thought 1900 was a leap year, and spreadsheets using the "1900 year system" will, intentionally, consider 2/29/1900 a valid date.

The Macintosh version of Excel offers a choice (under Preferences->Calculation) to set a 1900 or a 1904 year system.  The reason for this was that older Macs would only support dates after 1/1/1904 (because of the Lotus-established confusion over the year 1900) - Apple's way of dealing with this compatibility issue.

Windows versions of Excel, living in the PC World, maintained Lotus compatibility regarding dates.

Yes, it's a bug and a confusion, but a societal one, not an Microsoft Excel one.  How to maintain awareness of the murkiness around such date calculations on computers is also a societal problem.

See http://support.microsoft.com/kb/180162 for MS's documentation of this issue.

This bug is unacceptable. Many small businesses rely on excel for financial reporting, invoicing etc. I found it suprising that microsoft didn't even put it on the front page and finding it in support or the knowledge base was pathetic. Shame on you microsoft.... You want to move us to the latest products but now I have decided to roll my company back to office 2003 and will keep them there. I have also decided to cancel the vista roll out due to this. Looks like xp for years to come.....

another one for u guys to think abt:

65534.9999 * 0.9999999999 = 0.0000000000000

I can't wade through all 140 comments.. but - is there any correlation between the 65535 number and the amount of possible ports a system has? 0-65535 (if you count zero as a number - 65536?) Could this be an OS/app interaction thing? Seems like that's a pretty "famous" number, eh?

@aah!

Yes, 65,536 is 2^16, that's why this number crops up in these various unrelated fields - it is the largest number of values or states that can be represented by two bytes

(For nitpickers - I mean two bytes can have 65,536 different values, including 0. The largest of these values is of course 65,535)

@Jamie Baranec

I'd be interested to see your tests.

If I have this:

A1=850*77.1

A2=A1

A3=AVERAGE(A1:A2)

then A3 displays 100,000 - not because it has calculated that the average is one hundred thousand, but the result has the same display bug.

If I change A2=A1+1 this displays 100,001 (the second bug region). Now the average correctly displays as 65,535.5

If I fill down A2 to (say) A10 so they show the sequence 100,000; 100,001; 65,537, 65,538 etc. then the average of any range of more than two of these is accurate and as expected.

Same goes for MIN and MAX - if the cell with the bug in is the one which the MIN or MAX function picks out then the result of the MIN or MAX will have the display error. It does not alter the value so it suffers from exactly the same problem.

Try the numbers 65,534; 850*77.1 and 99,000

The MAX of these will be 99,000, the MIN will be 65,534. If you change the 65,534 for 65,535, then the MIN will show 100,000 since it pulls the inaccurate answer to (850*77.1) which is infinitessimally less than 65,535, and then displays it incorrectly.

Adding 2 to any of these AVERAGE, MIN or MAX results demonstrates that the answer they hold is mathematically consistent and stored correctly (ie 'very near to' 65,535 or 65,536), and suffers exactly the same display bug.

One of their flagship products, Excel , has been caught making errors doing simple multiplication. According to a Microsoft Developer blog: Yesterday evening we were alerted to an issue in Excel 2007 (and Excel Services 2007) involving calculation of

The solution: throw away Excel and use OpenOffice Calc.

What simpleton would believe that Closed Source, proprietary software is better than Open Source.

I'll use the old, tired, example of purchasing an Automobile.

Would you purchase an Automobile with the hood (bonnet) welded shut. You could not fill it with gasoline unless you went to a special station.  You were not allowed to perform your own maintenance on the automobile and further more a manual providing instructions on how to perform maintenance on the vehicle did not exist.

On the other hand, you can buy an automobile where you can open the hood. You can perform your own maintenance, there are thousands of individuals who can perform maintenance on this vehicle and you are able to interchange parts as you wish.

Open Source software, such as Open Office, passes MUCH MUCH more QA scrutiny than Microsoft Excel will ever see. At best, Microsoft has less than 100 QA testers working on Excel....which is precisely why such a bug got into the wild.

Open Office has EASILY 100 times this amount of QA scrutiny, precisely because ANYONE with any coding experience can act as a QA tester.

You don't see simple math errors such as 77.1*850 occurring in Open Office do you????

You've been drinking too much of the Microsoft Kool-Aid.

Oh, and by the way, both Google and IBM are backing Open Office

at excel prior to 2007 there are 256 Columns, which is the square root of 65536...!! Beware! Coincidence!! :-O

ok, joke aside.

@all the ignorant and lazy posters: You don't know about 65536 and computers? For you it's a similar stupid number like 34957? Fine, i have an advise:

a) get knowledge, f.i. by reading other posts where people share their wisdom

b) or just don't post and avoid to make your ignorance known to the public on forum related to *computer-matters*

@the rest: bad luck, it's just another mark on the microsoft joke list. However imo, what makes the problem less severe is that the error is so severe :-/

meaning: since 100K is "almost* the double of 65K the miscalc should be quite evident to a user (if you know your numbers and have some idea/feeling of what you want to get beforehand)

Good luck in fixing it, to the MS-guys!

---------

"Who doesn't work makes no errors"

@jakers

2^16 is 2 to the 16th power (= 65536).

If you've been wondering what the "x^y" key is on in the Microsoft Calculator's "scientific" view, here's your answer.

it was mentioned way above, but the majority of posters proved that they haven't read it, so here it goes again:

http://www.joelonsoftware.com/items/2007/09/26b.html

Experienced similar problems tallying far lesser values on a modest library grant budget. After having to ascertain, cell by cell, whether correct formulas were used in currency conversion and network tariff costs and then modifying line by line, I finally resorted to my handy Casio fx-260 solar.

Oy!

For all the Open Office posts

If Open Office is so great why do you read this blog ?

Hui...

Well, playing around to see if I could get the numbers in question, I found the following:

A1 = 65535

A2 = A1 * 1.00000000001 = 65535 (yeah, floating point)

A3 = A2 * 1.00000000001 = 65535.9

A4 = A3 * 1.00000000001 = 0

A5 = A4 + 1 = 1

So there seems to be something more than just the representation going wrong here.

Example of the similar problem in Excel 2003 where the "displayed" value is the same but the HEX equivalents are wrong...

DEC 850 77.1 65535

HEX 352 4D FFFE

DEC 85 771 65535

HEX 55 303 FFFF

* although the displayed value is correct in both cases, the HEX values are off by 1 - (using DEC2HEX addin function)

When this settles down, please post the guilty code fragment.

It would be interesting to see how such an obscure bug could be written.  There's probably a good example that can be (mis)quoted for years to come when describing coding practices.  

(We all make mistakes, not a criticism of Microsoft.  And cannot blame your QA for not testing all 2^64 floating point values.)

@Maria

Are you sure? I can't repoduce that at all

What version are you using?

Is that the right number of zeroes in 1.00000000001? How on earth do you get the .9 ?

@empower

I have resisted replying to the hordes in this thread and others claiming that DEC2HEX is broken, but since it seems to be getting so much repetition, let's all say it together:

"DEC2HEX converts a decimal _integer_ to hexadecimal"

It is not wrong that DEC2HEX(10.9)=A, and nor is it wrong that DEC2HEX(65,535-some small amount)=FFFE, since the operand is not an integer it gets truncated first.

Hi Guys

I've sorted Error traps for my Workbooks until sorted

Heres an example:

=IF(F8=850 & F9=77.1,F8*F9,F8*77.099999999)

Where F8 and F9 contain the offending calculation data

Seems to me like Microsoft can't figure out 65536 to me... Since it errors out when rows = > 65536. And with the previous poster saying the square root of 65536 is 256, the max number of columns.. Coincidence? Geesh...

Hi,

I have converted Excel's 97-2003 format Add-In

i.e. xyz.xla to 2007 format xyz.xlam. Now I am getting more no of columns and rows for xyz.xlam. Can I preserve 255 columns in xyz.xlam ???? I want the Addin's extention to .xlam, please help !!!

I had a great time meeting people at Cornell and Columbia this past week. Answered a bunch of questions

@Hui

I read this blog because at work my unimaginative employer has been trapped into a corporate licencing arrangement.  Information on this and other bugs could affect my work so it is need to know.

At home I use open office or gnumeric.  I dont think it's any accident that the big commercial product which is closed source (and raking in millions) falls prey to this kind of error while the open source product does not.

Cheers

Could you please tell me what Bank is using Excel 2007?

I would like to deposit 850 times the amount of 77.1$ and then withdraw 100K

Thanks

When Excel fix this will the ordinary Joe Blow user such as myself be sent the fix automagically?

probably you can loan 77.1$, 850 times from the bank.

I haven't read the whole thread yet, but I'd like to add a point about OpenOffice: OpenOffice uses wrappers around floating-point operations to correct some round-off errors. That's why it appears to be "correct", but in fact, you should be aware that this is not reliable and can hide real user-side bugs. There has been a discussion about this in the French users mailing-list in February/March 2005 (thread "[users-fr] [CALC] Calculs imprécis").

@AdamV

That was cut and paste, but I must say it changed after I saved and re-opened the file (prompted by your post). Strange.

Emmh, I like it how light hearted this issue is taken but MS is charging $650 for this *COMMERCIAL* suite, can we expect a refund?

MSDN Blogs上Excel组发布了报告，指称Excel 2007里存在一个重大bug，会将一些正确计算结果应为65535的两个数乘法，错误的计算成100000。这个问题只存在于Microsoft...

Neil Rubenking's (of PC Magazine programming fame) analysis:

"If it were just 850*77.1 that gave a wrong answer, we could probably work around that. But there are tons of other problem numbers, as I discovered for myself. I set up a spreadsheet to divide 65,535 by every number from 1 to 65,535 itself, then multiply the number by that result. So, for example, it divided 65,535 by 26 to get 2,520.577. Then it multiplied 26 by 2,520.577 to get...

100,000?! Over ten thousand of these simple calculations gave the wrong answer."

http://www.computerworld.com/blogs/node/6254

Existe un bug en la capa de visualización de datos de Excel 2007, este error causa que Excel muestre

Existe un bug en la capa de visualización de datos de Excel 2007, este error causa que Excel muestre

To: Hui...

# re: Calculation Issue Update

> Saturday, September 29, 2007 8:59 PM by Hui...

>

> For all the Open Office posts

>

> If Open Office is so great why do you read this blog ?

>

> Hui...

The reason is very simple, the same as you look for cartoons and comics @newspapers ... to have fun laughing !

Hui... enjoy Excel, 'because you're worth it'

I don't know why everyone is making big deal about it.  It depends on how often you do calculation of 77.1*850 and I highly doubt it'll be used other than this comment section.  I recommend to use the previous version of Excel.  Easy solution~

Its not the single calculation of 77.1*850. If you take 65535, divide by each integer between 1 and 65535, take that result, multiply it against the original divisor, you will get 10,023 wrong calculations out of 65535. over 15% of the solutions.

If you commonly round your numbers off, like many financial companies do in their spreadsheets, those become a REAL value of 100,000 , not the 65535 MS says is residing in memory.

cleve is right, but take more simple case. You buy some items of 38.55 each, for your 34 offices over the region e.g. 50 pcs for each office. If you put formula (=product(..)) in excel 2007 you will get folloving result. There is also a result for 51 pcs ...

Price/item   38.55 38.55

Offices           34        34

Items per office   50        51

Total      100 000.00    66 845.70

It means you can get 77.1*850 in various ways and it could happen more frequently then a meteor strikes ...

Ok, so the inaccuracy of floating point numbers has been discussed to death but, if this is simply a display issue, I'd be interested to see some technical description of where the display formatting goes wrong.

Hi to all, I don't know if you notice that the number 65,535 is the some number of lines as excel 2003 have.

Its NOT just a display issue. Export your data to a CSV file (which many companies do for batch processing on Main frames etc...). The error is sent out as real data, not put back to 65535 like is claimed.

also do any calculation of any of the combinations that produce the error and round the answer. Again it locks in the wrong answer. that IS a calculation error.

I think MS is just being very wary of admitting a calculation error, when there are so many people out there just waiting to claim a mistake based on an "Admitted" calculation error in the software.

I need by Microsoft a workaround proposal. In detail how should a user verify, that the values are correct.

this "workaround is needed until the bugfix is available. It makes no sense to design a workaround myself (i cant ensure that my solution would be 100% correct - this has to be done by the owner of the product: Microsoft.

It is very probably an error from the base 2 to base 10 conversion. So, any calculation that uses the result of such a conversion will have an incorrect result too.

BTW, I wonder what Excel does for its conversion. Does it always convert the number to a 15-decimal precision, then round to fewer decimals if need be? That could explain why so few values are concerned by the bug.

Cleve is absolutely right, ROUND locks in the erroneous value, as does TEXT and Paste > Values as well as exporting to CSV.

Read more about which functions ignore the display error here:

http://veroblog.wordpress.com/2007/10/02/excel-2007-bug-shows-wrong-answers-to-simple-multiplications/

Also note that you can kludge ROUNDDOWN to replace ROUND as a workaround. Assuming you want to rounnd the result in A1 to the nearest second decimal place (so .123 is .12 but .345 is .35), you can use:

=ROUNDDOWN(A1,2)+(ROUNDUP(A1+0.005,2)-ROUNDDOWN(A1+0.005,2))-0.01

Clunky, but it works and does not propagate the  error value of 100,000.

Interesting - the fix "[buggy display] + 1 - 1" fixes ALL of the the 2^16 numbers, but NONE of the 2^16-1 numbers (according to my test of nearly 9000 of each).

I'm wondering why 100000 is the number displayed by the bug.  It's 32 in binary, but what does that have to do with it?  Guess I'll have to wait for the final explanation for that one.

@AdamV

From what you say on your blog, the MOD function is incorrect: "MOD(850 * 77.1, n) gives -7.27596E-12 for integer values of n including 1 and 5 (which should both return 0)". I'm not sure that they should be 0 because of rounding error, but a negative value is incorrect anyway.

FYI, conversion algorithms use the modulo function, so it seems that the real bug could be in the modulo function, and the display/conversion bug reported here would just be a consequence.

Thanks for the feedback, Vincent.

I suppose what I am saying about MOD is similar to my point about ROUNDDOWN. If 850*77.1 calculated correctly with no floating point inaccuracy, I (as a user without wanting to know about how floating point math works) would expect MOD(850*77.1,5) to be exactly equivalent to MOD(65535,5) and equal to 0.

I agree with you that no matter what else this should never return a negative value of any size.

Incidentally, I've edited in some clarification of what I meant when I said that ABS is safe ot use:

http://veroblog.wordpress.com/2007/10/02/excel-2007-bug-shows-wrong-answers-to-simple-multiplications/#more-127

I recently found out this site http://www.daheiser.info/ and that these issues are certainly not minor.

Also, I found it amazing that the spreadsheet from the Star Office suite, doesn't have the problems Excel has. "I've heard" Octave behaves in the same way respect from Matlab.

Trouble is I want to keep on working with Excel (and Matlab), and it seems there is not an easy way out. What I find incredible is that MS doesn't seem to care, and statistically Excel has been an awful solution since day one. Hope someone cares sometime.

The "easy way out" is to continue using Office 2003 until a fix is out, since that does not have the bug. Alternatively read up about how the bug actually behaves and avoid the main issue which is 'locking in' a wrong value.

"statistically Excel has been an awful solution since day one" - what exactly do you mean by that? Is that would be be why millions of people choose to use it then?

I'm amazed to see how many people are really quick to say "use Foo Office, it does not have this bug".

No previous version of Excel has had this particular rendering oddity either - in over 20 years of product life, that's not a bad record.

Yes, it should not have happened. Yes, it could easily affect real-world calculations.

I'm not convinced that the QA of an OpenSource product would have been any better in the same circumstances. The file format was being changed, and the UI, and the calculation engine (for multi-threading) and the sheet limitations, and the function embedding limits ... all changing at the same time. I appreciate that many pairs of eyes reviewing code offers a huge benefit, but I don't accept that this makes it _impossible_ for similar bugs to arise, just less likely. I also don't believe the information and patching mechanisms for these products are as straightforward for enterprise change management, so a bug would be  harder to fix in a controlled manner.

/disclaimer. I'm biased. I use Excel extensively and find the rich feature set really valuable. I don't sell it and I have no shares in Microsoft. I just happen to have been using the product for about 14 years and have come to like it.

Hi, I am saving excel 2007 file with different extension say .es5 When I  reopen the file I get a message file is corrupted and have to recover. After recovery It shows following log-

 <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

- <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

 <logFileName>error001680_01.xml</logFileName>

 <summary>Errors were detected in file 'C:\East 2006\Source 5.1\Ui\East 5\a111111111111.es5'</summary>

- <removedRecords summary="Following is a list of removed records:">

 <removedRecord>Removed Records: Merge cells from /xl/worksheets/sheet2.xml part</removedRecord>

 </removedRecords>

 </recoveryLog>

What can be the reason ??? Is there any problem with excel 2007 for saving file with diffrent extension ????

please reply

There seem to be much more problems with the MOD function. For instance, MOD(2^27,1) doesn't evaluate to a number!

More precisely, it seems that MOD(n*2^27-1,n) works, but not MOD(n*2^27,n) and not for some/most/all numbers above n*2^27 either. I wonder if there is a relation with the fact that 27 is about 53/2, as Excel uses a 53-bit arithmetic (IEEE-754 double precision).

I just worked with the list of numbers in Erich Neuwirth's post above (September 27).  I think these corrections are necessary (also, sorted from smallest to largest):

65535-2^(-37)

65535-2^(-36)

65535-2^(-36)-2^(-37)

65535-2^(-35)

65535-2^(-35)-2^(-37)

65535-2^(-35)-2^(-36)

65536-2^(-37)

65536-2^(-36)

65536-2^(-36)-2^(-37)

65536-2^(-35)

65536-2^(-35)-2^(-37)

65536-2^(-35)-2^(-36)

Oops, I got the sorting wrong in my last post.  Each block of 6 is from largest to smallest.

uh, its so long discussion

these all show us the restriction of human thought

in other word, no body's perfect

regards for u all guys, in searching the weakness of human products

"statistically Excel has been an awful solution since day one"

The above statement simply means that Excel has always been found lacking as a statistics tool, contrary to what Microsoft marketing department would like people to believe. Many studies conducted by statisticians have showed that fact over and over. It may not have been since day one but (from memory) the first study was conducted circa 1994, more than a decade ago.

"Is that why millions of people choose to use it then ?"

No, millions of people use it because through monopolistic practices, Microsoft made sure that retailers sold computers bundled with their own software, backstabbing the competition. Once they cornered the market, they started changing Office (Word, Excel, ...) file specifications so that competitors would never be able to develop fully compatible software. Despite those shenanigans, lawyers (for example) still prefer WordPerfect to Ms Office.

So, here we are today, stuck with a program that has been advertised to the tune of $ 450 millions (according to Chris Liddell, Microsoft Chief Financial Officer) but can't even accurately display the result of a basic multiplication. Other office suites may contain bugs but they neither suffer from dyslexia nor perform a vanishing act with our data.

"Steve" wrote:

"Good Lord people! Floating point operations are by their very nature innacurate, period. It's always been this way, doesn't matter if we're talking PC, Macintosh, mainframe, nor Windows/Mac/Unix/whatever. Anyone who truly understands computers accepts this as an axiom."

Um, anyone who truly understands computers also understands that displaying 100000 for a floating point value close to 65535 is not an issue of floating point rounding inaccuracy but an actual bug in the code displaying the number.

This is clearly a bug.  It may not be a true calculation bug, but it is also not simply a display bug.  It is an output bug -> garbage out!

What I do not understand is why Microsoft has not better alerted their Excel 2007 customers of this very real issue.  

I could find no reference to this issue on the main Excel page:

http://office.microsoft.com/en-us/excel/FX100487621033.aspx

Perhaps, it could be listed under "What's new in Excel 2007".

All sarcasm aside, the only place you find references to Excel 2007's output error is on the forums, which are rarely if ever visited by the vast majority of Microsoft's customers.

The responsible thing to do would be for Microsoft to clearly let folks know about this issue and provide a real workaround until it is fixed.  e.g. Allow MS Excel 2007 customers to download and install Excel 2003.

I don't get it.  

Russ wrote:

"

"Steve" wrote:

"Good Lord people! Floating point operations are by their very nature innacurate, period. It's always been this way, doesn't matter if we're talking PC, Macintosh, mainframe, nor Windows/Mac/Unix/whatever. Anyone who truly understands computers accepts this as an axiom."

Um, anyone who truly understands computers also understands that displaying 100000 for a floating point value close to 65535 is not an issue of floating point rounding inaccuracy but an actual bug in the code displaying the number.

"

John (that's me) wrote:

But Russ, what's the "Um" for? Steve was addressing ONLY the floating point issue that some other posters didn't seem to understand and he made a perfectly reasonable point -- one that's floated past you, it seems. :)

All in all, let me know when the fix is out. Nobody cares about anything you say here.

Hopefully, the patch will be out on Patch Tuesday, the 9th.

Excel released 1/30/07 ; problem discovered 09/07.  Wow, I think I'll go back to Visicalc, I'm shaking in my boots.

Is this the best that Microsoft did in four years until release Office 2007?

Great programmers!

It has been more than a week since this bug was acknowledged by Microsoft, however, the only thing I can find on microsoft.com that is related to this issue are links to this blog.  

Where is the update?  When will we know how/when this will be fixed?  

Is it me, or does it seem that Microsoft has gone all quiet about this?

Copied from my Quattro Pro:

+850*77.1 65535.000000000000000

Copied from my Symphony

a1=859

b1=77.1

a1*b1= 65535

From my Quattro Pro 4 for DOS.

77.1*850=65535

Quattro Pro for DOS still has a better graphing tool than any version of Excel; or other spreadsheets for that matter.  The only issue is lack of modern printer support, which is not a problem because my late 80s laserjet is still doing fine.

Regarding Hollis's and funnybroad's comment about why Microsoft isn't speaking up on this matter:  

I don't know why they're not providing more information.

But I do know is that when they finally release a patch, the description of the patch should make for interesting reading.  That will be when most Excel users even hear about the calculation bug for the first time.

Some folks will panic if the bug is described in clear terms.  But if the Microsoft obfuscators handle it properly, it'll sound like an enhancement.

I'd bet Microsoft is expending as much time and effort for the Microsoft Update description as they as on the patch itself.

I, too, am disappointed that Microsoft isn't more forthcoming.  I bet that there are members of the Excel team who want to say more, but are being stifled by the mucky-mucks higher up.  Sad.  

Good grief, Charlie Brown!

From PTab for Smartphone.

77.1*850=65535

This is a really great little spreadsheet for the Smartphone. I use it on my Moto Q. I recommend that everyone who is concerned about the Excel problem just do all their spreadsheets on a Smartphone. Sure, your thumbs will get tired, and there's no printer support, and you can only see about 3 columns by 8 rows without squinting, but at least you won't have to worry about accidentally getting 100,000 instead of 65535!

Here is a link talking on the issue in hand - http://software-testing-zone.blogspot.com/2007/09/multiplication-bug-in-microsoft-excel.html

Once the patch is ready for distribution could you ensure that it can seamlessly applied via the “Updates” folder during the installation of Office 2007.

Excel 2003 SP2 has inconsistencies as well.

Using these same numbers:

=DEC2HEX(850*77.1,4)

gives "FFFE"

Similarly DEC2OCT give 177776 and not 177777.

HOWEVER, if you do add a TRUNC function inside the DEC2HEX, it seems (so far as I have tested it) to regain its brain...

=DEC2HEX(TRUNC(850*77.1),4)

gives "FFFF"

Now, if you read the help within Excel for DEC2HEX, it states that this function ALREADY does a truncation?!?!? So how does adding another TRUNC() function help it?????!!!

Microsoft needs to review this a bit closer, and they need to open up the scope of their review to include more than just Excel 2007!

I have spreadsheets for calculating tables in embedded controllers at rely on exactly these types of calculations.

ro_ablate, I assume you realize that with floating-point, 850*77.1 is really 65534.99999999995. DEC2HEX likely does a "direct" truncation by throwing away the decimals, while TRUNC tries to account for rounding errors.

Even though it may not be entirely obvious, it makes sense, because DEC2HEX (and OCT) represent what would get if you did a direct conversion without accounting for floating-point errors - your microcontroller would arrive at the same result if you converted to an integer with a simple typecast. However, TRUNC is more general use, so it can't work like that - therefore, that one takes rounding errors into account.

Sample code showing the issue:

C:\tmp>type test.c

#include <stdio.h>

int main() {

double a = 850 * 77.1;

printf("%d",(int)a);

return 0;

}

C:\tmp>gcc test.c

C:\tmp>a

65534

If Micro$oft wasnt so busy building mulitple layers of anti-thieft security, anti-competition exploits, previous version incompatibility and trying to force all software into using IE as its base process, then something as simple as a floating point error would not have crept into the software which for all practical purposes is rewritten far too often.

My advice to M$ - Write a solid base product with no frills, then make every feature a downloadable add-on.

Integration is nice but not if it brakes or introduces errors into the core product.

If its a simple display issue, why is it taking 2+ weeks to get a simple patch out?

And don't give me the testing excuse. Other companies have gotten patches for worse things out faster.

This is unacceptable from a multi-billion dollar a year company.

Dear Billy Cates,

I´m a doctor, yesterday I calculated a dosage for my patient (yes, it´s your son!) in excel as 77.1kg*850=100000mg. He´s dead, you don´t know it yet. 65535mg would save his life.

Try to resurrect him with the service pack.

Sincerely yours ...

Looks like the fix is out for this - KB943075 (Excel 2007 hotfix package: October 9, 2007)

Download at: http://download.microsoft.com/download/6/1/3/61343075-aa12-4152-a761-fccc16d6cef4/office-kb943075-fullfile-x86-glb.exe

Il y a tout juste deux semaines, un bug concernant Excel 2007 (et Excel Services du coup) avait été remonté

Thank you Excel User. We have been waiting for the update since a long time.

"So how does 4.1-4 equal 0.09999999999999999600?"

4.1 is not representible exactly using binary floating-point numbers because its binary representation is not finite. Neither is 0.1. If this bothers you, use a tool (other than Excel) that performs calculations using decimal floating-point numbers or rational numbers instead.

To a binary floating-point implementation, THERE IS NO 0.1.

"all come out correct. Interesting"

Probably they just lead to different rounding.

Hurray! A fix at last!

http://veroblog.wordpress.com/2007/10/10/excel-2007-calculation-bug-fix-released-after-two-weeks/

You may have recently read of an issue in Excel 2007 involving calculation of numbers around 65,535 where

Is the fix possibly gonna be this wrapped up in an update!

Where ever the input number = 77.1 do this:

(77.1-0.0000000001)

???????

Therefore:

850*(77.1-0.0000000001) = 65535 (displayed)

Apparently Microsoft Excel 2007 can't do it's times tables. Microsoft employee David Gainer, has posted on the Excel Blog that when computer users try to get Excel 2007 to multiply some numbers,...

Still somthings fishy after the patch.

try =("8:00:00"-"7:00:00")*1440 and then add 20 decimals. I get 59,9999999999999000 should have been 60

So has anyone noticed that 65535 is coincidently the number of rows you can have in Excel?

re: Jakob

Thats in all versions, not just 2007.

Will this thread die when there have been 850*77.1 comments posted?

@humanbeing:

>I´m a doctor, yesterday I calculated a dosage

>for my patient (yes, it´s your son!) in excel

>as 77.1kg*850=100000mg. He´s dead, you don´t

>know it yet. 65535mg would save his life.

I'd be more worried about being off by a factor of a million (kg/mg) than I would by being off by around a factor of 1.5.

@scottb

I'd guess that the kg supposedly relates the the ficticious patients weight (850mg per kilo of body mass?)

my worksheet contains 5 columns for stock market : <open>,<high>, <low>, <close> and <result>

QUERY: if <close> is greater than <open> and <low>, then <result> must be "strong" or if <close> is less than <open> and <high>,then <result> must be poor.

I tried three different  formulas below for this query ,but they doesn't work:

A2=<open>

B2=<high>

C2=<low>

D2=<close>

E2=<result>

formula for E2

=OR(IF(AND(A2<D2,C2<D2),"Strong",FALSE ),IF(AND(A2>D2,B2>D2),"Poor",FALSE))

////////////////////////////////

=OR(IF(AND(A2<D2,C2<D2),"Strong","" ),IF(AND(A2>D2,B2>D2),"Poor",""))

////////////////////////////////

=OR(IF(AND(A2<D2,C2<D2),"Strong" ),IF(AND(A2>D2,B2>D2),"Poor"))

I find it difficult to formulate the above condition with available functions in excel 2007. Please mail me solution or formula at my e-mail 'ashokabnave@yahoo.co.in'

...I'm not referring to Excel as Excel, but as a calculation program. If such program gives uncorrect results, that's not a good program, and it should be corrected as soon as possible. Or recalled.

Microsoft should have done a better beta-testing and bugfixing before releasing it to the market.

End of story.

I HOPE there will be more serious beta-testing from now on.

There is an interesting post over at blogs.msdn.com

I just loaded the hot fix, I think there is still an error.

Prior listings warned aboout =dec2hex(850*77.1)returning fffe, when it should return ffff.

The hot fixed excel returns fffe for the calculated value of (850*77.1).

Do the following:

a1 =850*77.1 (65535)

b1 =dec2hex(a1) (fffe)

c1 =hex2dec(b1) (65534)

d1 =sqrt(850*77.1)^2 (65535)

e1 =dec2hex(d1) (ffff)

now I can see b1 is not equal to e1

I am using old version. I did not know the calculation problem with excel 2007. Such a great explanation.

Thank you

Damon Thomas

http://www.excelfilerecovery.com

http://www.excelfilerepair.com

I have another oddity of wrong calculations. I am using Excel 2003.

If you SUM up the following numbers, EXCEL will show 98703.22.  If you do a comparison with the absolute value of 98703.22 and the SUM calculation, is shows there is a difference of ~  0.00000000035

Hence the formular =IF(98703.22<>SUM(B2:B275),"X"," ")  with the range B2 to B275 containing the following values, will show an X:

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

2,796.64

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

428.12

428.12

428.12

428.12

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

349.58

428.12

428.12

349.58

349.58

349.58

349.58

349.58

349.58

349.58

@Thaddeus

The Dec2Hex non-bug has been widely misreported.

I have replied more fully to the comment on the same subject that you left on my blog here:

http://veroblog.wordpress.com/2007/10/10/excel-2007-calculation-bug-fix-released-after-two-weeks/

Has anyone seen this before...

If working on an Excel worksheet, Round(1408.7705,3) gives the expected result of 1408.771

If I do the same in VB, I get 1408.770

Any ideas why the VB example is rounding down not up?

Daniel, the first Round you use is a worksheet function. To reproduce this in VB try

Application.WorksheetFunction.Round(1408.7705, 3)

Why the second one rounds down for 5 I couldn't tell you, at school it was round up for 5 and above!

@Daniel Tackley: it's likely bankers' rounding (round-to-even).  See http://en.wikipedia.org/wiki/Banker%27s_rounding.

Shame on you, Microsoft! Don't think it was programmers' fault: bloatware life cycle is so difficult to tackle.