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