Re: INT defect: Please try this on 2007 for me
- From: "JoeU2004" <joeu2004@xxxxxxxxxxx>
- Date: Wed, 14 Oct 2009 16:48:53 -0700
[Sorry for the late response. I got busy.]
"Charles Williams" <Charles@xxxxxxxxxxxxxxxxxx> wrote:
Most real-world spreadsheets contain a chain of calculations
where the effect of the floating-point calculations makes an
absolute comparison of the low-order bits such as you are
attempting a pointless exercise.
I have answered many dozens of queries posted in these forums involving
real-world situations where the root cause is related to the side-effects of
binary floating point representation and calculations.
My examples are not intended to demonstrate the real-world situation per se,
but the root cause. Often they are a distillation of the real-world
calculations. Sometimes I choose my parameters to demonstrate the boundary
conditions of those calculations. But in all cases, the results my examples
can arise in normal Excel calculations.
Many people have expressed appreciation of the detailed examples that I
provide because it helps them to visualize an esoteric concept that is
otherwise beyond their scope of expertise.
As you have demonstrated, if you need such accuracy in
the low-order bits you should not be using Excel.
It is not that I (and the users that I represent) "need" such accuracy. It
is the fact that Excel calculations employ or result in such accuracy,
contrary to misguided conventional beliefs, demonstrated by your comments,
and misleading MS documentation. Consequently, users stumble into
situations where that accuracy leads to unexpected results.
To be sure, the solution is for users to ensure that the result, at least,
has no more accuracy than they expect, usually by the prolific, but prudent
use of ROUND.
but I think (without going into the gory details) that
Excel is working as designed in your example.
You are entitled to your opinion.
But even if INT is working "as designed" (i.e. a conscious choice by the
implementer), the rhetorical question is: is the design correct?
Again, that is a matter of opinion.
I find it difficult to believe that any reasonable person would not be
surprised by A1-INT(A1) returning a negative result for positive A1, since
that cannot be explained by knowledge of the binary representation alone.
Just as I would find it difficult to believe that any reasonable person
would not be surprised by the fact that the constant 40000.848 is displayed
as 40000.847999999 when formatted to 15 significant digits. Again, that
cannot be explained by knowledge of the binary representation alone.
But I guess you would insist the latter is not a problem since "Excel is
working as designed" ;-).
----- original message -----
"Charles Williams" <Charles@xxxxxxxxxxxxxxxxxx> wrote in message
news:4ad1a1ae$0$2486$db0fefd9@xxxxxxxxxxxxxxxxx
It's sometimes interesting to try to work out exactly what chain of
calculations Excel is performing under the covers, but I think (without
going into the gory details) that Excel is working as designed in your
example.
Most real-world spreadsheets contain a chain of calculations where the
effect of the floating-point calculations makes an absolute comparison of
the low-order bits such as you are attempting a pointless exercise.
As you have demonstrated, if you need such accuracy in the low-order bits
you should not be using Excel.
regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com
"JoeU2004" <joeu2004@xxxxxxxxxxx> wrote in message
news:eJhC3nfSKHA.1796@xxxxxxxxxxxxxxxxxxxxxxx
PS....
"Charles Williams" <Charles@xxxxxxxxxxxxxxxxxx> wrote:This approximates to 15 significant decimal digits, as
you have found. This is the way it is designed to work
I responded:Sorry, but you are wrong.
... Not that I would object to a calculation option that would do just
that, namely: force the result of all formulas to be rounded to 15
significant digits, not unlike the "Precision as displayed" option, but
more generally applied.
That would legitimatize some of the heuristics that Excel has implemented
to try to ameliorate the aberrations due to binary floating point
arithmetic. For example, as I noted in response to Jerry elsewhere in
this thread, the presumptive behavior of INT would work in my specific
example -- A1-INT(A1) -- if the formula in A1 had been rounded to 15
significant digits.
I imagine the performance cost would be equivalent to the performance
cost of "Precision as displayed".
Arguably, there would be still an issue with subexpressions in formulas.
For example, 123456789 - 0.0000004 - INT(123456789 - 0.0000004) might
still be a problem, at least not without an expensive solution.
Moreover, it would not mask the effects of all aberrations caused by
binary floating point arithmetic. But it should eliminate the "hidden"
effects, effects that cannot be seen even when formatting the cell to
display 15 significant digits.
In any case, Jerry's comments do suggest a work-around to the specific
anomaly that I presented in the original posting, to wit:
VALUE(A1&"")-INT(A1) is well-behaved, given the presumptive behavior of
INT.
----- original message -----
"JoeU2004" <joeu2004@xxxxxxxxxxx> wrote in message
news:OdnMwpZSKHA.504@xxxxxxxxxxxxxxxxxxxxxxx
"Charles Williams" <Charles@xxxxxxxxxxxxxxxxxx> wrote in message
news:4ad04917$0$2538$da0feed9@xxxxxxxxxxxxxxxxx
All numbers in Excel are IEEE Binary floating point.
Of course. Whadaya think I meant when I wrote, "You need to look at the
exact conversion of the internal binary representation -- that is,
beyond
the first 15 significant digits"?
And wheredaya think I'm getting all those extra digits when I wrote that
123456789 - 0.0000004 is "about 123456788.999999,598"?
(It is exactly 123456788.999999,59766864776611328125. Or if you prefer
binary, &h419D6F34,53FFFFE5, which is a stylistic way of writing
0x419D6F3453FFFFE5 in C.)
This approximates to 15 significant decimal digits, as
you have found. This is the way it is designed to work
Sorry, but you are wrong. Reason it out for yourself.
If you were right, how could I subtract 0.0000004 from 123456789 in the
first place? And how could subtracting 0.0000005 have different
results?
(Hint: Reread my original posting. I answer those questions.)
And if A1 is positive, A1-INT(A1) should never return a negative number.
Zero, maybe; but not negative.
Finally, for your edification, try the following experiment.
A1: 12345789
A2: =A1 + 2^-26
A3: =A1-A2=0
Format A1 and A2 to 15 significant digits (e.g. Scientific with 14 dp).
Note that A1 and A2 __appear__ to be the same. But A3 being FALSE
proves
they are not.
Even though Excel formatting is limited to 15 significant digits (as is
data entry), arithmetic is performed to the full precision of 64-bit
floating point, which is more than 15 significant digits.
(Actually, pairwise operations are performed to the precision of 80-bit
floating point, then rounded to 64-bit floating point.)
as outlined in Excel Help etc.
I'm afraid that MS tech writers tend to over-simplify technical
explanations, and they often get it totally wrong.
For example, http://support.microsoft.com/kb/78113 states: "although
Excel
can store numbers from 1.79769313486232E308 to 2.2250738585072E-308, it
can
only do so within 15 digits of precision".
That is flatly incorrect, as it relates to results from arithmetic
operations, as I demonstrate above.
(And arguably, if the sentence above were intended to refer to storing
constants, not results of calculations, the tech writer is still wrong
because constants have a more limited range, at least in Excel 2003.)
----- original message -----
"Charles Williams" <Charles@xxxxxxxxxxxxxxxxxx> wrote in message
news:4ad04917$0$2538$da0feed9@xxxxxxxxxxxxxxxxx
All numbers in Excel are IEEE Binary floating point.
This approximates to 15 significant decimal digits, as you have found.
This is the way it is designed to work, and is as outlined in Excel
Help
etc.
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com
"JoeU2004" <joeu2004@xxxxxxxxxxx> wrote in message
news:Ov5QzJYSKHA.1796@xxxxxxxxxxxxxxxxxxxxxxx
Would someone who has Excel 2007 running on an Intel-compatible
computer
(i.e. not a Mac) please try the examples below and post your results
here?
I posted this inquiry to an Excel 2010 blog. Not sure if/when I'll
hear
back from them.
(Extra credit: If you have Excel 2010 running on an Intel-compatible
computer, feel to try these examples and post your results here, too.)
Has the following Excel 2003 problem been fixed in 2010 (or 2007)?
INT(123456789 - 0.0000004) returns 123456789 instead of 123456788.
This causes a problem in formulas like the following: if A1 is
=123456789-0.0000004, =A1-INT(A1) is negative unexpectedly,
about -4.917E-07 when formatted as General.
In contrast, INT(123456789 - 0.0000005) returns 123456788 as expected,
and myInt(123456789 - 0.0000004) returns 123456788, where myInt() is
the
following UDF:
Function myInt(x as Double) as Double
myInt = Int(x)
End Function
Note that 123456789 - 0.0000004 is represented internally as about
123456788.999999,598, whereas 123456789 - 0.0000005 is about
123456788.999999,493 internally. (The comma demarcates the first 15
significant digits.)
So I suspect that the Excel INT algorithm is effectively, albeit
perhaps
unintentionally, rounding its argument to 15 significant digits before
truncating to an integer. It shouldn't.
Indeed, the largest expression involving 123456789 that returns an
incorrect INT value is 123456789 - 33*2^-26, which is represented
internally as about 123456788.999999,508, whereas 123456789 - 34*2^-26
is
about 123456789.999999,493 internally.
As you might imagine, the problem is not limited to 123456789 -
0.0000004. And the problem will not appear with some combinations that
you might think are similar, e.g. 100000000 - 0.0000004.
You need to look at the exact conversion of the internal binary
representation -- that is, beyond the first 15 significant digits --
to
determine whether or not to expect a problem. Most people cannot; I
can.
.
- Follow-Ups:
- Re: INT defect: Please try this on 2007 for me
- From: Charles Williams
- Re: INT defect: Please try this on 2007 for me
- References:
- INT defect: Please try this on 2007 for me
- From: JoeU2004
- Re: INT defect: Please try this on 2007 for me
- From: Charles Williams
- Re: INT defect: Please try this on 2007 for me
- From: JoeU2004
- Re: INT defect: Please try this on 2007 for me
- From: JoeU2004
- Re: INT defect: Please try this on 2007 for me
- From: Charles Williams
- INT defect: Please try this on 2007 for me
- Prev by Date: Re: How do I only make a cell available if an option from a different validated cell is selected in Excel?
- Next by Date: Re: INT defect: Please try this on 2007 for me
- Previous by thread: Re: INT defect: Please try this on 2007 for me
- Next by thread: Re: INT defect: Please try this on 2007 for me
- Index(es):
Relevant Pages
|
