GCD Function doesn't work in some cases

[GCD_Dilemma]

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 !
(I've broken down this formula into its simplest elements;
the numbers normally will be filled with variables or cell references.)

 

[Harlan Grove]

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.

 

[GCD_Dilemma]

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 !!!
********************************

 

[Jerry W. Lewis]

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]

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 !
************************************************

 

[hgrove]

GCD_Dilemma wrote...
...

I guess the key is "binary fractions" & "IEEE approximations." Sinc 4/10 is NOT
an infinitely repeating DECIMAL number, I assume you mean that t store it in
1's & 0's with a sign & all, it DOES end up being a repeating BINAR number
when converted & stored in a computer's registers & therein lies th IEEE
approximation when converting back & forth.
Correct.

Evidently, there are some conversion "losses" & differences whe storing &
working with numbers & evaluations that to the user's eye & logi should equate
to being the same but in the practical PC world do not work out tha

way.

Correct. The key is to realize that spreadsheets are actually a form o
programming language, so you must adopt a programmer's eye & logi
rather than those of a user. Until you do so, this sort of thing wil
drive you nuts. Of course, this assumes you don't want to be drive
nuts.

Therefore, things like (1.4 - 1) does not necessarily equal (0.4) I ALL CASES
when being stored & manipulated on the computer (but in some case 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

Yup. Exactly as it does in any other programming language using IEE
double precision floating point that doesn't perform constant ter
elimination at compile time.

The point here is that the 1.4 term starts with a bit for 2^0 (the 1 t
the left of the decimal point), while the 0.4 term begins with the 2^-
bit. Thus the two are truncated at different bits, as Jerry mentioned.

THIS IS HOW IEEE STANDARD 754 WORKS.

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 u is for
numbers 3 & 4 when the resulting outcome hovers right around 0 on it journey
from positive to negative numbers. All other numbers seem to yiel perfect
whole integers.

...

If you change 1.4 to 1.7, your B10 anomalies may differ.

To repeat, as long as you maintain a 'user' perspective, you'l
repeatedly bang your head against walls. You need to adopt a programme
perspective