Calculation error 850 times 77,1

[Mantvydas]

I wonder if Microsoft is going to do anything about the =850*77,1 being
equal 100000 instead of 65535 error in Excel 2007. Any news of patch,
anyone?

Mantvydas

 

[Nick Hodge]

They are certainly aware, I would expect something today or tomorrow

--

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog

 

[Harlan Grove]

They are certainly aware, . . .

No doubt, but will they publicly acknowledge this bug, er, feature?

I would expect something today or tomorrow

Optimist.

Not if it's deeply buried in the formula parser's code. If this is
flawed bit bashing, it may not be the easiest thing to FIND much less
fix. OTOH, if this is due to some flaw in the interface between the
formula parser and how parsed values are stored in memory, it could
take even longer.

Regardless, it seems clear Excel doesn't just parse formula values,
dispatch them to the FPU, perform arithmetic in the FPU, and store
double precision results in memory. At the very least it seems they
could use a remedial seminar on the KISS principle.

 

[Michael C]

Optimist.

Not if it's deeply buried in the formula parser's code. If this is
flawed bit bashing, it may not be the easiest thing to FIND much less
fix. OTOH, if this is due to some flaw in the interface between the
formula parser and how parsed values are stored in memory, it could
take even longer.

Regardless, it seems clear Excel doesn't just parse formula values,
dispatch them to the FPU, perform arithmetic in the FPU, and store
double precision results in memory. At the very least it seems they
could use a remedial seminar on the KISS principle.

Actually the bug appears to show this is exactly what excel is doing. It's
just having trouble converting the double result into text.

Michael

 

[Harlan Grove]

Actually the bug appears to show this is exactly what excel is doing. It's
just having trouble converting the double result into text.

If so, then it'd only be the cell's .Text property that was screwed up, and
all calculations using these false-appearing 100000s would use the cell's
actual value, 65535. However, that appears not to be the case since, as
others have reported, with A1 containing a problem formula returning a
false-appearing 100000, the formulas =A1-1 and =A1+1 use DIFFERENT values
for A1, and I believe others have reported that SUM and AVERAGE functions
treat such A1 values differently. That lack of consistency in downstream
calculations is a very strong indication that this is more than just a
rendering problem.

But let's assume it were just rendering. Why is 65535 a special case? As
others have shown, 65534 and 65536 are occasionally displayed correctly, as
are noninteger values between those two integers. How about another test to
see if this were only a rendering issue: try the formula

=(850*77.1)

Also, does changing the number format change the value displayed?

 

[Michael C]

If so, then it'd only be the cell's .Text property that was screwed up,

It is. The cells Value and Value2 properties report the correct result,
always.

and all calculations using these false-appearing 100000s would use the
cell's actual value, 65535. However, that appears not to be the case
since, as others have reported, with A1 containing a problem formula
returning a false-appearing 100000, the formulas =A1-1 and =A1+1 use
DIFFERENT values for A1, and I believe others have reported that SUM and
AVERAGE functions treat such A1 values differently. That lack of
consistency in downstream calculations is a very strong indication that
this is more than just a rendering problem.

This is quite interesting and I'm not sure I fully understand what is going
on but let me explain what I think is happening. In EVERY situation the
actual value in the cell is correct. And in EVERY function I have tested
(only the ones you mentioned here, including A1+1 and A1-1) the correct
value is used (ie 65535 is used, not 100,000). The reason some functions
show the same problem is that they have the same bug converting the value to
text. Here's my explanation for each:

MAX:
This uses the value of the cell correctly and calculates the correct result.
It just displays the result wrong. This is no suprise as the result of
Max(A1) is going to be 65535 and hence the same bug will appear. If you get
the Max of 65535 (which is showing 100,000) and 70,000 then the result is
70,0000. if it was using the text of A1 then the result would be 100,000.

SUM and Average:
Same applies here as applies to max. Sum(A1) will result in 65535 which will
display as 100,000. Same for average. If you Sum 65535 (shown as 100,000)
and 65 then you get 65600, not 100,100.

A1-1:
This uses the value of A1 and sutracts 1 correctly

A1+1:
This is the odd one in that it makes it look like excel is using a different
method to add 1 than it is to subtract, but that is not the case. If you do
A1+2 then you get the correct result of 65537. The reason A1+1 shows 100,001
is just that it happens to have hit on the same bug. The sequence of events
would be 1) Get value from A1, which is 65535 2) Add 1 to that value to get
65536 3) Convert that value to text to display on screen. At that point the
same bug would be encountered.

But let's assume it were just rendering. Why is 65535 a special case?

It's just a bug. Why is impossible to answer without looking at the source
code. Possibly they used a < sign when they should have used <=. It's likely
the bug is only an issue with a very small range of values, or possibly even
just one single value, eg It's possible that 65535.00000000001234 renders as
100,000 where 65535.00000000001235 does not.

as are noninteger values between those two integers. How about another
test to see if this were only a rendering issue: try the formula

=(850*77.1)

This still gives the problem. Any formulae that returns the same result will
show the bug I believe.

Also, does changing the number format change the value displayed?

No, it still displays incorrectly.

Michael

 

[xvoland]

850*77 + 850*0,1 = 65535

 

[Mark Lincoln]

There's at least one other thread (with 125 messages!) on this
subject.

Here's the word from the Microsoft Excel blog:

http://blogs.msdn.com/excel/archive/2007/09/25/calculation-issue-update.aspx

Mark Lincoln

 

[Gordon]

This is quite interesting and I'm not sure I fully understand what is
going on but let me explain what I think is happening. In EVERY situation
the actual value in the cell is correct. And in EVERY function I have
tested (only the ones you mentioned here, including A1+1 and A1-1) the
correct value is used (ie 65535 is used, not 100,000).

Interesting. I entered the calculation "850*77.1" in A1 and got the 100000
answer as expected. In cell C1 I entered the formula "=A1-1" and got the
correct answer, 65534. In cell E1 I entered the formula "=A1+1" and got the
unexpected answer, 100001!

 

[Ed Hansberry, MS-MVP/Mobile Devices]

Interesting. I entered the calculation "850*77.1" in A1 and got the 100000
answer as expected. In cell C1 I entered the formula "=A1-1" and got the
correct answer, 65534. In cell E1 I entered the formula "=A1+1" and got the
unexpected answer, 100001!

"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."

When you do 850*77.1, the error is in the first set of numbers.
Adding 1, you jump to the second set. Add .5, 2, or 1.1 and you
won't see the problem.
--
__________________________________________________________________________________
Ed Hansberry (Please do *NOT* email me. Post here for the benefit of all)
What is on my Pocket PC? http://www.ehansberry.com/
Microsoft MVP - Mobile Devices www.pocketpc.com
What is an MVP? -

 

[Michael C]

Interesting. I entered the calculation "850*77.1" in A1 and got the 100000
answer as expected. In cell C1 I entered the formula "=A1-1" and got the
correct answer, 65534. In cell E1 I entered the formula "=A1+1" and got
the unexpected answer, 100001!

Clearly you didn't read the post you were replying to because this was
explained in that very post.

 

[Guest]

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