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