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.)

"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.

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.

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.

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.

GCD_Dilemma wrote...
...
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.

Correct.

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.

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

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

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

The point here is that the 1.4 term starts with a bit for 2^0 (the 1 to
the left of the decimal point), while the 0.4 term begins with the 2^-1
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 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.
...

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

It's not something a user should have to "program" around.

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


--
hgrove
------------------------------------------------------------------------
hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432
View this thread: http://www.excelforum.com/showthread...hreadid=276133