The answer to the Excel 2007 65,535 bug

formatting link

Reply to
ChairmanOfTheBored
Loading thread data ...

Le Sat, 27 Oct 2007 22:54:37 -0700, ChairmanOfTheBored a écrit:

I like the conclusion: "And let's face it -- do you really want the bright sparks who work there now, and manage to break lots of perfectly good working code -- rewriting the core calculating engine in Excel? Better keep them busy adding and removing dancing paper clips all day long."

--
Thanks,
Fred.
Reply to
Fred Bartoli

Except that they coded out the dancing paper clips years ago.

Reply to
ChairmanOfTheBored

ha, what they hell are you using WORD size fields?

--
"I\'m never wrong, once i thought i was, but was mistaken"
Real Programmers Do things like this.
 Click to see the full signature
Reply to
Jamie

But he didn't explain anything.

John

Reply to
John Larkin

Then you didn't read it.

Reply to
ChairmanOfTheBored

He did, but what he explained was the 'well known' facts about floating point representation of numbers. 'Everyone' knows that floating point will give rounding errors. What is really bad is that the display formatting so badly screws up a result that a number near 65535 ends up displaying as 100000. Thats a real bug. Personally, if a result ends up as close as the floating point representation to (say) 2.99999999999 will allow, then I would rather see that than have the formatter magically transform it into 3, just so it looks prettier.

--
Regards,

Adrian Jansen           adrianjansen at internode dot on dot net
 Click to see the full signature
Reply to
Adrian Jansen

Of course I read it. He didn't explain the origin of the bug at all.

From the web page:

Q: What caused the bug?

A: I'm not sure exactly, since I don't have the code.

If you understand it, please explain it to us.

John

Reply to
John Larkin

Le Mon, 29 Oct 2007 08:46:19 +1000, Adrian Jansen a écrit:

Maybe that's what they did? I mean: if a result ends up as close as the floating point representation to (say) 65535 will allow, then they have the formatter magically transform it into 100000 so it looks prettier.

--
Thanks,
Fred.
Reply to
Fred Bartoli

Back in the day when Intel shipped all those Pentiums with the floating point bug, there was a small formula published that would reveal the floating point error. I noticed that with a "good" CPU, and using Excel (I don't recall version) that the same formula would fail repeatedly. On the otherhand the test executable passed for the CPU.

--
Joe Leikhim K4SAT
"The RFI-EMI-GUY"©
 Click to see the full signature
Reply to
**THE-RFI-EMI-GUY**

You can examine the bug from a more hardware oriented bitwise viewpoint by creating a couple of simple formulae to approach the failing limit condition by adding ever decreasing multiples of 2^-n.

For practical reasons it is a lot more reliable to compute x[n+1] = (x[n]+x0)/2 starting with x0 = 65534 and x1 = 65535 - or (65535 and 65536 if your prefer to get 100001).

Cell A1: 65535 A2: =(A1+A$1+1)/2 copy down as far as you like about 40 lines to see the failure occur as the last 3 bits of the 52bit binary mantissa come into play. It is unlcear how the GUI guys managed to screw this up, but alas it is typical of XL2007. The values are correct but are displayed incorrectly in the main spreadsheet. VBA Str() function works OK.

Obviously you can test the limit case approaching from the above with A2: (A1+A$1-1)/2 so far I haven't found any of these. It would be interesting to know exactly what contorted programming logic produced this failure mode.

It goes belly up if any of the last 3 bits in the mantissa are non zero (and some other unknown condition is also met). It is supsicious that it happens so close to 2^16. I had a quick poke around other (2^n)-1 values without success but it was only a cursory effort.

My biggest gripe by far about XL2007 is that clients who use Excel to analyse moderate amounts of scientific instrument data have found that XY graphs of just a few thousand points with the axes manually scaled run 1-2 *orders* of magnitude slower. It is pretty slow even with default scaling compared to earlier versions, but it crawls along at glacial speed when manual scaling is used to examine regions of interest.

A couple of seconds to draw a few calibration graphs is fine, but 20s every time is mind numbing.

My advice is stick with earlier versions until they sort out the bugs in this manifestly broken release.

There is incidentally a hotfix for this bug, but you would have to be feeling brave to use it.

formatting link

Wait for SP1 before considering paying good money for it (maybe even SP2).

Regards, Martin Brown

Reply to
Martin Brown

It triggers software interrupt 0 even in the 8086/8. The remainder does go into AH or DX or EDX.

Many years ago when looking at a hex dump (with ASCII on the right) of one of my programs I noticed a divide char ÷ where my DIV AL instruction was. It's the same code F6.

--
Reply in group, but if emailing add another
zero, and remove the last word.
 Click to see the full signature
Reply to
Tom Del Rosso

Well, he sort of did. Its a bug in the routine that formats floating point values.

This leads me to a couple of questions: Where else (in what other products) might this defective formatting routine be used? If this bug is limited to Excel, why does Excel use its own libraries instead of a shared lib? One that is widely used is more likely to be deemed worthy of careful scrutiny than a limited version. If various Microsoft projects are in fact writing their own libraries, in how many other places are we (or more accurately, all you poor fools still using MS products) likely to encounter bugs or incompatibilities due to everyone writing their own code?

Software reuse is (was?) supposed to be important to all the CS gurus. What went wrong at MS?

--
Paul Hovnanian	paul@hovnanian.com
-----------------------------------------------------------------------
 Click to see the full signature
Reply to
Paul Hovnanian P.E.

--------------- Somewhere inside Excel is a function that converts binary numbers to strings for displaying. This is the code that has the bug that causes a few numbers which are extremely close to 65,535 to be formatted incorrectly as 100,000.

---------------

Apparently, that's all they've got.

But, it goes on to say, "This is still only a bug in the number formatting code..." so it doesn't break the calculation, just the display, apparently.

Cheers! Rich

Reply to
Rich Grise

My original point of this was, many manuals have errors in their publications. for example, the one I have states the remainder goes in the AL, AX or EAX and quotient goes in the uppers like the DX ,EDX etc.. Not only that. when you reference the IDIV and DIV for example, it states they're both unsigned.. ( another error)..

On top of all that, no where does it state that the remainder which we know is in the upper regs AH, DX, or EDX effects the next calculation because it's assumed that any value in the remainder register is from a prior DIV operation where it might be an extended precision operating taking place and there for, used as a carry.. etc.. THe Book, Assembler Language for Real programmers only.. this thing is full of errors and left out needed information.

it's not only this reference with these errors. I find all over the net what looks like exact reprints how ever, there are some that are correct ! you just have to keep looking until you find information that actually works with the test results you're getting.

I find my turbo references are most accurate.

--
"I\'m never wrong, once i thought i was, but was mistaken"
Real Programmers Do things like this.
 Click to see the full signature
Reply to
Jamie

I can't find my favorite book right now, because it's in a box, but I'll make a note to check it later. It's "The Processor and Coprocessor" from MS Press.

--
Reply in group, but if emailing add another
zero, and remove the last word.
 Click to see the full signature
Reply to
Tom Del Rosso

Jamie jamie_ka1lpa_not_valid_after_ka1lpa snipped-for-privacy@charter.net posted to sci.electronics.design:

As i posted elsewhere in this thread, accurate data from the Microsoft MASM manual has the correct and reasonably complete information.

Reply to
JosephKK

ElectronDepot website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.