Thanks Jerry, that clears things up a bit more.
I guess the key is "binary fractions" & "IEEE approximations."
Since 4/10 is NOT an infinitely repeating DECIMAL number,
I assume you mean that to store it in 1's & 0's with a sign & all,
it DOES end up being a repeating BINARY number when converted
& stored in a computer's registers & therein lies the IEEE
approximation when converting back & forth.
Evidently, there are some conversion "losses" & differences when
storing & working with numbers & evaluations that to the user's
eye & logic should equate to being the same but in the practical PC
world do not work out that way.
Therefore, things like (1.4 - 1) does not necessarily equal (0.4)
IN ALL CASES when being stored & manipulated on the computer
(but in some cases they will, shown below).
So, in Excel (to 30 decimal places):
=((0.4)*10-4)
yields: 0.000000000000000000000000000000
but
=((1.4-1)*10-4)
yields: -0.000000000000000888178419700125
Interesting & vexxing at the same time.
Vexxing in that:
=((1.4-1)*10-B10)
If B10 ranges from 0 to 9, the only time this loss
apparently shows up is for numbers 3 & 4 when
the resulting outcome hovers right around 0
on its journey from positive to negative numbers.
All other numbers seem to yield perfect whole integers.
I'm not sure if the resultant outcome being near zero
is significant when causing these conversion-type truncating errors.
Thanks very much though !
************************************************
"Jerry W. Lewis" wrote:
Since 4/10 is an infinitely repeating binary fraction, approximations to
1.4-1 and 0.4 truncate at different locations, with the result that
while the IEEE approximation to to (0.4)*10 is exactly 4, the
approximation to (1.4-1)*10 is <4, as you can verify by
=((1.4-1)*10-4)
The outer parentheses are required to prevent Excel from arbitrarily
zeroing the result since it would be a final operation involving a
difference of numbers that are equal to the advertised limit of 15
decimal places.
Jerry
GCD_Dilemma wrote:
Thank You.
That worked, although I don't know how your explanation
covers the situation where
=GCD((1.4-1)*10,10)
comes out wrong, but
=GCD((0.4)*10,10)
comes out right. ???
The 1st example (1.4 - 1) should evaluate into the 2nd example
(0.4) BEFORE it's multiplied & given to the GCD function.
0.4 is not even like a repeating decimal such as 1/3=0.3333333...
And it's disconcerting to realize that (1.4-1)*10 and 4
aren't the same thing when being passed on to a function.
(Not to mention that mathematically they are the same.)
This takes seemingly working functions that work in some cases
but may not work in others unless one is aware of these
"quirks" behind the scenes.
It's not something a user should have to "program" around.
Thanks for the info though.
Microsoft: Grrrr !!!
********************************
"Harlan Grove" wrote:
"GCD_Dilemma" wrote...
How come the following in Excel 2003:
=GCD((1.4-1)*10,10)
yields 1 rather than 2, but
=GCD((0.4)*10,10)
yields the correct answer 2 ?
=GCD(4,10)
also correctly yields 2 !
Because by default Excel uses IEEE double precision floating point math, so
ANY calculation involving fractions other than sums of negative powers of 2
(e.g., 1/2, 1/4, 1/8, 1/16, etc.) is subject to roundoff error in the same
way that representing 1/3 as 0.3333 or any other finite string of 3s to the
right of the decimal point is.
Since GCD and LCM only make sense in the context of integers, ensure that
your arguments to either are integers. For instance,
=GCD(ROUND((1.4-1)*10,0),10)
returns 2, as expected.
|