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.