Excel 2007 multiplication bug - and more...

[JL]

I'm surprized no one has brought up the issue recently blown wide open on
slashdot.org .... http://it.slashdot.org/article.pl?sid=07/09/24/2339203

excel 2007 cannot handle simple math like =850*77.1
you get 100,000 instead of 65,535

any timeline for a fix?
how on earth did this get missed in your beta and QA and testing???

Microsoft, your flagship office app is about to sink because of fundamental
holes, please repair and assure us there are no other problems?
--
JL

[Ed Ferrero]

Public announcement from Microsoft here
http://blogs.msdn.com:80/excel/archive/2007/09/25/calculation-issue-update.aspx

Ed Ferrero
www.edferrero.com

[Bob I]

Heck, that was old news by the time that was posted. Blown open indeed,
try reading the postings here,first?

[JL]

Thanks muchly - that does give much more info about it...

however - no timeline is given :(

btw, i did try to search for excel 2007 bug BEFORE i posted - but the search
gave me zero results, lol
--
JL

[JL]

I searched for "excel 2007 multiplication bug" - and got ZERO results - so i
posted... i also looked at the couple of pages - and didn't see a topic on
topic.

if you search for "excel 2007 multiplication bug" now, you'll see ONLY my
post ;p

but thanks for your very constructive answer

--
JL

[Bob I]

Yes, lots of people tend to start off their searches with too many
constraints, and as a result never find what they are looking for,
instead of starting big and winnowing the results. "Excel bug" and
"excel 2007 bug" work much better. Hope this helps in your future searches.

[Harald Staff]

It's not "simple math like =850*77.1", it's only exactly that and a few more
similar number combinations, as you can see at the msdn page. How often do
you believe this is happening ? Feel free to use another spreadsheet
figuring it out.

Best wishes Harald

"JL" <J...@discussions.microsoft.com> wrote in message
news:DDFD4E27-671B-4ECC...@microsoft.com...

[Peo Sjoblom]

It was posted 9/22/07 in the Excel newsgroup.

--

Regards,

Peo Sjoblom

"JL" <J...@discussions.microsoft.com> wrote in message
news:DDFD4E27-671B-4ECC...@microsoft.com...

[JL]

How often do I believe it's happening? - well, thats pretty hard to calculate
you realize, I have no problem agreeing that the frequency is very small.

Even very, very small if that makes you feel better?

So what?

How do you trust your business or house payments or anything to something
which has KNOWN math problems? I don't have the time to test excel for MS, I
don't know on which other types of calculations this can happen... it happens
with stuff that equates out to 2^16 and involves decimals, maybe it happens
at 2^32 also, or 2^64.

I commonly use spreadsheets which have many many thosands of cells and
equations - are you saying I should just check each cell manually to make
sure none of them are of a form which causes this bug?

You can feel free to do that if YOU wish - MY time is not nearly so abundant.
--
JL

[Jon Peltier]

Then stick with a tried and true version. Any of the sequence Excel 2000
through 2003 have been pretty well vetted by now. If you read these
newsgroups, you are no doubt familiar with issues people are having with the
new version. Mostly it's due to user error or unfamiliarity with the new
interface, but if this unfamiliarity is going to waste time, it is probably
not worth the upgrade.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

"JL" <J...@discussions.microsoft.com> wrote in message

news:40B47A0D-0A84-4A29...@microsoft.com...

[Bob I]

It's a rendering bug that was discovered after literally hundreds of
millions of man-hours of use. It was discovered, they are working on it,
it will be corrected. While there is the remote chance that this bug
would cause you to miss some detail, I would wager that your personal
errors in creating and using the spreadsheet would and should be the
problem of concern. Trust? I don't "trust" software. If the number
doesn't look right, I cross check it.

[AdamV]

It's at least a dozen combinations of number pairs with one or two decimal
places, and therefore could be considered easy to hit by accident.
Even if you are doing financial calculations such as quanitity x price and
price is only two decimal places for cents or pennies or whatever, this is
still a very real risk.

See here for examples:
http://veroblog.wordpress.com/2007/10/02/excel-2007-bug-shows-wrong-answers-to-simple-multiplications/

There are literally thousands of other more obscure combinations where one
or other of the multiplicands have several decimal digits. These do all lead
to a small number of results which have the rendering error (as indicated on
the MSDN page) but that's not the same as saying there are only a handful of
ways to generate the bug.
--
Adam Vero
MCP, MOS Master, MLSE, CWNA
http://veroblog.wordpress.com
http://www.meteorit.co.uk

[JL]

Yes, which is why it's amazing to think that free software like OpenOffice
and KDE's Koffice get the correct result, as does prior versions of ms office,

makes you wonder what exactly it is you're paying for?

oh well, I suppose every large empire must fall, we're just seeing the
fundamental cracks here :P move along... nothing to see

--
JL

[Jon Peltier]

You're seeing the likely result of an optimization gone awry.

A regular person says, "If it works, don't fix it."

An engineer says, "If it works, you haven't fiddled with it enough."

A programmer says, "If it works, you haven't added enough features."

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

"JL" <J...@discussions.microsoft.com> wrote in message

news:56BD4CCB-642C-47D3...@microsoft.com...

[James G.]

Is this patch available on a CD, I am using a dial-up connection and this
file woud take me a few days to download!!

[Bob I]

35 meg will take 3 hours give or take.

[JL]

35 meg 3 hours? - sounds about right if you're using vista on a 100 megabit
LAN :)

according to
http://forums.microsoft.com/forums/ShowPost.aspx?PostID=2259619&SiteID=17

:) lol - i wonder when they'll fix vista file copy/move/delete performance?

--
JL

[Jerry W. Lewis]

Microsoft recently published a patch that appears to fix this
http://support.microsoft.com/kb/943075
Oddly, this patch (though already out) was not bundled with the "essential"
patches that I downloaded last week along with the trial version of Office
2007.

Values of 2^16-1-d (whether as a formula result or a constant), where d was
too small (2^-37 <= d <= 6*2^-37) to properly impact the 15-digit decimal
representation, displayed as 100000 despite still having the correct
underlying value. Values of 2^16-d displayed as 100001 despite still having
the correct underlying value. Interestingly, this seems to have been a new
intersection in Excel 2007 of two old bugs that have existed at least since
version 4, and probably since the inception of Excel.

1. There appears to have been a set of millions of valid binary numbers
(that included fractional parts) which for whatever reason were not permitted
as constant values in Excel, but were supported as the result of
calculations. The values like this that I am aware of rounded away the
trailing bits in the final three positions of a binary floating point number.
For values like 0.5 +/- d, this rounding made a perverse kind of sense as an
early attempt at the "optimization" that was introduced in 1997
http://support.microsoft.com/kb/78113
which "optimization" has led to numerous questions where a formula that by
itself appears to return zero doesn't behave like zero in a LOOKUP or IF
function or in a larger formula (because at the binary level, the result is
not and should not be zero). This rounding made less sense with numbers
like, 0.5000012207031250266453525910037569701671600341796875+/-d, where even
the "rounded" number could not be fully displayed in 15 decimal digits. This
longstanding bug appears to have been completely fixed in the original
production release of 2007, before application of the current patch.

2. There appears to have been a non-overlapping (AFAIK) set of millions of
decimal fractions that could not be displayed properly
http://support.microsoft.com/kb/161234
admits to x.848 displaying as x.8479999999 for x an integer between 2^15 and
2^16, but there are millions of other decimal fractions that were similarly
mis-displayed
http://groups.google.com/group/microsoft.public.excel.misc/msg/1b2d9f986ce8e65b
I was not previously aware of any number in this set whose incorrect display
was off by more than 1 in the 15th digit; as a result, fixing this bug has
seemed to have little or no priority with MS until now.

I believe both of these longstanding bugs to be related to the current bug
for the following reasons:

- It does not make sense that a current change to the display engine
capable of causing this current bug could have survived its testing phase
without uncovering this bug.

- If the process of displaying results (formulas as well as constants)
first went through the filter of bug 1 before being passed to the display
engine, then the 2007 patch for bug 1, would mean that display of these
impacted values had never been tested, yet the need to test their display
could easily have been overlooked.

- The patch for the current problem appears to also fully patch bug 2,
while preserving the patch for bug 1 (thank you MS for not simply restoring
bug 1).

Jerry