A
adam.vero
Oh well, at least there's a fix available now:
http://veroblog.wordpress.com/2007/10/10/excel-2007-calculation-bug-fix-released-after-two-weeks/
Oh well, at least there's a fix available now:
http://veroblog.wordpress.com/2007/10/10/excel-2007-calculation-bug-fix-released-after-two-weeks/
E
Ed Hansberry, MS-MVP/Mobile Devices
Or directly from:
http://support.microsoft.com/default.aspx/kb/943075
--
__________________________________________________________________________________
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? -
M
MindscapeProductions
This is really amusing:
850 * 77.1 = 100000
850 * (7.71 * 10) = 100000
850 * (.771 * 100) = 65535
850 * (.0771 * 1000) = 65535
850 * (.00771 * 10000) = 100000
850 * (771 / 10) = 100000
850 * (7710 / 100) = 100000
How would you explain that?
D
Dave Peterson
You didn't install the patch mentioned earlier in this thread???
E
Ed Hansberry, MS-MVP/Mobile Devices
What is amusing is you apparently read none of the explanations
for it that have been repeatedly posted to this and other groups
http://blogs.msdn.com/excel/archive/2007/09/25/calculation-issue-update.aspx
nor did you apply the patch
http://blogs.msdn.com/excel/archive/2007/10/09/calculation-issue-update-fix-available.aspx
--
__________________________________________________________________________________
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? -
M
mkt982
Dear sir
referring to this problem I studied and peruse it and I found out that
it's (2^16)+1 and I think that it has been solved in windows vista and
i think the number of (2^16)+1 can not hold more than 16 bit
referring to this problem I studied and peruse it and I found out that
it's (2^16)+1 and I think that it has been solved in windows vista and
i think the number of (2^16)+1 can not hold more than 16 bit
M
MindscapeProductions
I was only shown the thread a few days ago, I didn't read all the way
through since there was not a "Last" option. I just found it amusing
that there was such a discrepancy. It was an opinion. I do not
believe I will be using Excel 2007 much, so I do not feel it necessary
to download the patch. Also, because I find it so amusing, I wish to
verify how many of the other bugs are correct. So sorry for bumping a
solved topic, I was just including information I thought had not been
noted upon.
M
Michael C
"Ed Hansberry, MS-MVP/Mobile Devices"
"Prerequisites
There are no prerequisites for installing this hotfix."
Surely having excel 2007 installed is a prerequisite
Michael
"Prerequisites
There are no prerequisites for installing this hotfix."
Surely having excel 2007 installed is a prerequisite
Michael
M
Michael C
The results you got are not unexpected. Floating point maths is not 100%
accurate and your calculations will return slightly different values (around
1e-10 difference or less at a guess). Only some very specific numbers have
the problem so some of your calcs hit on the problem and other's didn't.
accurate and your calculations will return slightly different values (around
1e-10 difference or less at a guess). Only some very specific numbers have
the problem so some of your calcs hit on the problem and other's didn't.
G
GN
This is simply because of the signed integer 2 byte limit 65535.
J
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
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
T
tw.climate
Any further word on this bug? Do I need to download a patch for this? (It's 2013 for crying out loud, this is still an issue?)
I'm getting the following in Excel:
42.2 - 42.0 = 0.200000000000003000
Notice the "3" in the 15th position.
Similarly:
42.3 - 42.1 = 0.199999999999996000
This is a HUGE problem as I have these numbers used in formulas elsewhere in my worksheet. Truly disgraceful.
I'm getting the following in Excel:
42.2 - 42.0 = 0.200000000000003000
Notice the "3" in the 15th position.
Similarly:
42.3 - 42.1 = 0.199999999999996000
This is a HUGE problem as I have these numbers used in formulas elsewhere in my worksheet. Truly disgraceful.
J
joeu2004
I don't have the context of the thread to which you are replying. But this
is not considered a defect. Instead, it is a limitation of the hardware
implementation that Excel relies on for numerical calculations, namely the
industry-standard 64-bit binary floating-point arithmetic.
In a nutshell, most non-integer values cannot be represented exactly
internally. And the infinitesimal differences sometimes become visible due
to arithmetic operations.
The general solution is for you to explicitly round calculations when you
require accuracy to a specific degree of precision. For example,
ROUND(42.2-42,1) and ROUND(42.3-42.1,1).
Alternatively, Excel provides the Precision As Displayed calculation option
(PAD). That automagically rounds the result of a formula to the number of
decimal places specified in the cell format.
However, I do not recommend PAD for most cases, especially a pre-existing
workbook. PAD can cause irreversible changes to some numeric constants,
which might have a sweeping effect on dependent calculations in the
workbook. Moreover, PAD alone does not remedy all problems. For example,
IF(42.2-42=0,TRUE) will still returns FALSE(!).
J
joeu2004
Errata.... "joeu2004 said:
Well, of course!
That should be:IF(42.2-42=0.2,TRUE) still returns FALSE(!).