You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
Yesterday we were alerted to an issue in Excel 2007 (and Excel Services 2007) involving calculation of numbers around 65,535. The Excel team would like to provide a description of the issue and explain what we're doing about it.
BackgroundYesterday evening we were alerted to an issue in Excel 2007 (and Excel Services 2007) involving calculation of numbers around 65,535. The first example that we heard about was =77.1*850, but it became clear from our testing as well as additional reports that this was just one instance where Excel 2007 would return a value of 100,000 instead of 65,535. The majority of these additional reports were focused on multiplication (ex. =5.1*12850; =10.2*6425; =20.4*3212.5 ), but our testing showed that this really didn't have anything do to with multiplication - it manifested itself with many but not all calculations in Excel that should have resulted in 65,535 (=65535*1 and =16383.75*4 worked for instance). Further testing showed a similar phenomenon with 65,536 as well. This issue only exists in Excel 2007, not previous versions.
The ProblemThis issue was introduced when we were making changes to the Excel calculation logic in the Office 2007 time frame. Specifically, Excel incorrectly displays the result of a calculation in 12 very specific cases (outlined below). The key here is that the issue is actually not in the calculation itself (the result of the calculation stored in Excel’s memory is correct), but only in the result that is shown in the sheet. Said another way, =850*77.1 will display an incorrect value, but if you then multiply the result by 2, you will get the correct answer (i.e. if A1 contains “=850*77.1”, and A2 contains “=A1*2”, A2 will return the correct answer of 131,070).
So what, specifically, are the values that cause this display problem? Of the 9.214*10^18 different floating point numbers (floating point on wikipedia) that Excel 2007 can store, there are 6 floating point numbers (using binary representation) between 65534.99999999995 and 65535, and 6 between 65535.99999999995 and 65536 that cause this problem. You can’t actually enter these numbers into Excel directly (since Excel will round to 15 digits on entry), but any calculation returning one of those results will display this issue if the results of the calculation are displayed in a cell. All other calculation results are not affected.
The SolutionWe take calculation in Excel very seriously and we do everything we can in order to ensure that calculation is correct for all cases. We’ve come up with a fix for this issue and are in the final phases of a broad test pass in order to ensure that the fix works and doesn’t introduce any additional issues - especially any other calculation issues. This fix then needs to make its way through our official build lab and onto a download site - which we expect to happen very soon. We’ll add another post once that’s taken place with a link to the download.
Comments: (205) Collapse
Joel Spolsky has a detailed explanation of the bug and what might have caused it on his blog: www.joelonsoftware.com/.../26b.html
[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....
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.
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
I hear thats now fixed in the file explorer, when can the office ppl do the same ?
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)
655536^-2^(-35)
655536^-2^(-36)
655536^-2^(-37)
655536^-2^(-35)-2^(-36)
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.