Why on Earth...
...would an engineer making such critical calculations use Excel, let alone anything written by Microshaft?
Microsoft has released a fix for a curious flaw that threatened to confound engineers who use the latest version of Excel. The patch fixes a bug that caused Excel 2007 and Excel Services 2007 to spit out incorrect results when calculating numbers around 65,535 and 65,536. The former number is the highest that can be …
really doesn't matter to me,
except as a curiosity.
What is much more vexing
is the unanswered question
of how it passed QA testing.
My personal guess is that it wasn't a bug in the calcuation of fp numbers but a bug in the display of them. This would explain why further calculations worked, and why automated testing could easily have missed it.
And two of the first three comments are by Softies. Nothing like blindly reciting what Microsoft has said. The only problem with that is that their explanation doesn't hold water. If you have functions such as ROUND() that use the buggy version of the number, then it's not just a display issue. As for the joelonsoftware.com link, that explained nothing about the bug's origin. It did a good job of explaining floating point calculation to the unwashed masses, but it didn't explain the cause of the bug at all. In fact, it was a very BAD article because it said the bug wasn't serious. I'd bet good money that accountants and engineers (if there are any that actually use Excel) consider it VERY serious.
Floating point calculations can always introduce errors. Excel (like any other program) has to 'decide' how to display numbers which are extremely close to other values (especially integers) to fool us puny humans that it knows the right answer.
The fact that 850*77.1 is a tiny fraction less than 65,535 when calculated in FP is a simple fact, and has been the case through all previous versions of Excel. UNless you are usually working to oodles of decimal places (another new ElReg numbering system quantity?) this will never matter.
When you use the ROUNDDOWN, INT, or TRUNC functions though, you would be pretty annoyed if it gave 65,534 as a result. These functions cleverly realise that 65,535 was probably the number that was meant to be there, and give that as the correct answer.
The bug in question was a display bug in so far as this logic broke down somewhere in it's attempts to appear to know the right answer, and it got it very wrong. However, the bug could propagate and become part of a real value when using certain functions or exporting data (eg to csv). See: http://veroblog.wordpress.com/2007/10/02/excel-2007-bug-shows-wrong-answers-to-simple-multiplications/
Despite two weeks of almost no updates and almost no press coverage by MS and Fleet Street, it's pleasing the bug now has a fix, let's hope it gets up on to Microsoft Update soon so everyone can get the fix as painlessly as possible.
I've used excel and seen it used at many of the worlds biggest engineering firms. Including UK's biggest. Hell, I've got 4 spreadsheets open behind this window as I type. It's an excellent tool for quick calcs on the fly that are just a bit too much drama for the calculator. It's Excel 2003 mind you as - like many others have found - 2007 falls on it's face when handling large amounts of data.
By the way have tried others like open office etc and they're worse than 07. There's still plenty of areas of engineering where there are no dedicated tools for the job or the ones there are hide dangerous assumptions, and excel fills the void.
Given the tone of post 1, I'm guessing you think I'm a "Softie"? Yet nothing in my post was pro-Microsoft. I suppose because I didn't call them "Micro$haft Bastards" I must be in cahoots with them, right?
As for the ROUND() issue, I can see a shortcut for such a function which would rely on code used in the display function, leading to the same issue, and still leaving the bug invisible to most automated testing.
Nowhere did I say that this was not a serious issue -- I merely pointed out that I believe the bug should have been caught at QA -- before the product even went beta -- and that the QA failure is what we should be raking MS over the coals for.
As for parroting MS, the only source I've read on this issue is El Reg. I don't regularly use Excel 2007 so I didn't particularly feel a need to research the issue in depth. My guess was based on the description of the issue and Occam's Razor.
FYI I consider myself a tech realist: I use various closed and open source solutions depending on which best fits my needs. (If Apple actually made an affordable product that did what I want, I'd even use that*)
*Cue the fanbois claiming that Apple's products are affordable (!) and functional (!!!!!)
Biting the hand that feeds IT © 1998–2020