Mixed Signals
Any sufficiently advanced information is indistinguishable from noise
Tuesday 25 September 2007, 10:44 AM
Major bug in MS Excel 2007
Got a copy of Excel 2007 handy?
Try multiplying 850 by 77.1 - the answer should be 65535.
Excel 2007 thinks it's 100000.
Older versions of the spreadsheet - as well as the latest versions of the OpenOffice.org and Gnumeric packages - get it right.
The problem, replicated here at ZDNet UK, was reported a couple of days ago on the Microsoft Excel newsgroup , and looks like an elementary programming cock-up.
Excel is the mainstay numerical application of many finance houses, banks and other institutions. A basic mathematical problem like this is extremely serious, and the fact that it exists and has slipped through testing is a major embarrassment for Microsoft.
No two - or two to the power of sixteen - ways about it.
More on this as we add it up...
PreviousComments on this post
It's something VERY odd with excels display code, if you subtract 1 you get 65334, adding one gives 100001 adding two gives 65537
Interestingly looking at the values from within a VBA macro the numbers are correct!
I'm sure the problem lies with the multiple layers of reformatting that continuously occurs.
Having told Excell I live in the UK, and want my dates in dd/mm/yyyy format, I have to continuously reformat the cells to redo these formatting simplicities. Then I decide I want to just type in Dec, 12, 2007 and it helpfully automatically reformats my text, even though reformatting is off, and I repeatedly tell it that cell is containing text, not a date, but it continuously tells me I am wrong and the cell should be a date.
Can someone please tell MS we need a "f***-**f - I know what I'm doing" button to make Excell do what most applications do, what I ask it to and not what it thinks I want to do.
or, gasp!, just try one of the other applications that DO work and do pay attention to my commands.
Is there any wonder the take--up of Office 2007 and Vista is modest?
Yes I like that one, quite mind blowing. It only seems to be that particular number though? Dividing 65535 by 77.1 gives 850 and dividing 65535 by 850 gives 77.1 which is all correct!
It's a good 'un. I'm not sure how serious it really is - as others have said, it only affects the display of the number, not its internal representation, and that's the sort of thing which is terribly difficult to write automated test sequences to catch. Something that's hard to find is also something that's not going to have a widespread impact.
Digging deeper, though, there's been a lot of rumbling about Microsoft's approach to arithmetic. It's based on IEEE 754, a rather elderly standard for floating point numeric representation, but isn't a standard implementation of same. People complain that the details change from release to release of Excel, meaning that you can't guarantee consistency for spreadsheets between versions, and that none of this is properly documented.
And as for what this means, philosophically and practically, for OOXML... well!
I was reading the MSDN Blogs today. it appears this has been hotfixed now, but won't be automatically rolled out until SP1
http://blogs.msdn.com/rextang/archive/2007/10/11/5398309.aspx
Thanks for pointing that out. Shame that MS hasn't said anything more about the bug - makes it difficult to evaluate what the implications actually were and are.
R
It seems that MS is afraid of going public on this issue. I would really appreciate of some official words on this even though it is going to be fixed with the release of SP1.
I've left a request for more info on the MSDN blog in question.
This is another angle on the open/closed question. If I were running a company critical application on a spreadsheet, I'd really want to know what had happened, how it had been fixed, what the chances are of knock-on or related problems, and so on. With open source, that comes for free. With closed source, you're dependent on decisions made by a company with different priorities to yours.
In this case (as in so, so many others), it's hard to see what MS has to gain by keeping things secret. It's not as if Excel contains valuable trade secrets - software can't, it's always amenable to examination, and spreadsheet technology is widely known anyway - or if MS could hide that there's a bug at all.
Why doesn't it want to keep its customers informed? Especially power spreadsheet users, which must include some of the most critical (in both senses) customers the company has. It's not as if Gnumeric isn't a serious contender: there's a good case for running important models on two independent products in tandem, especially if you can automate that process.
If Microsoft continues to be reticent on matters involving accuracy and reliability - to be fair, it may yet come up with the goods here - it can only make that case stronger.
Looks like we won't be getting any more info from Redmond
http://blogs.msdn.com/excel/archive/2007/10/09/calculation-issue-update-fix-available.aspx
"We're not planning to share details on this beyond what we've already communicated - i.e. that the issue occurred in formatting of floating point numbers near 65,565 and 65,536. It was code that we introduced as part of the calculation overhaul that we did for Excel 2007 however."
And "The patch is large because it contains updates for several components that use the Excel logic that showed this issue - you can see those components listed at the bottom of the KB article. Since several of those files are large, it makes the entire patch large."
Which is interesting - so either it's not a single bug in a common function, or there's an interface issue.
