MS Office Forum / Excel / New Users / October 2007
Calculation error 850 times 77,1
|
|
Thread rating:  |
Mantvydas - 25 Sep 2007 13:41 GMT 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 - 25 Sep 2007 21:06 GMT They are certainly aware, I would expect something today or tomorrow
 Signature HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS web: www.nickhodge.co.uk blog (non tech): www.nickhodge.co.uk/blog
>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 Harlan Grove - 25 Sep 2007 22:25 GMT "Nick Hodge" <nick_ho...@zen.co.uk> wrote...
>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 - 26 Sep 2007 01:48 GMT > Optimist. > [quoted text clipped - 8 lines] > 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 - 26 Sep 2007 02:58 GMT "Michael C" <mike@nospam.com> wrote...
>"Harlan Grove" <hrlngrv@aol.com> wrote in message ...
>>Regardless, it seems clear Excel doesn't just parse formula values, >>dispatch them to the FPU, perform arithmetic in the FPU, and store [quoted text clipped - 3 lines] >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 - 26 Sep 2007 05:15 GMT > 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 [quoted text clipped - 4 lines] > 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@gmail.com - 26 Sep 2007 12:16 GMT Gordon - 27 Sep 2007 09:37 GMT > 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 - 27 Sep 2007 11:52 GMT >> 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 [quoted text clipped - 6 lines] >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.
 Signature __________________________________________________________________________________ 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? - http://mvp.support.microsoft.com/
Michael C - 01 Oct 2007 04:47 GMT > 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.
Mark Lincoln - 26 Sep 2007 17:26 GMT 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
> They are certainly aware, I would expect something today or tomorrow > [quoted text clipped - 15 lines] > > - Show quoted text - Jerry W. Lewis - 22 Oct 2007 08:45 GMT 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
> 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
|
|
|