This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?

You will have to take it up with IEEE (Institute of Electrical and
Electronics Engineers). It is the international standard for floating point
arithmetic. XL complies with this standard. Once you have convinced them that
they are wrong I am sure the XL will fix your issue.
--
HTH...

Jim Thomlinson


"Jeff in GA" wrote:

This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?

And just for a little perspective on the error it is in the 15th significant
digit. With that level of precision you can measuse the distance from here to
the sun accuarately to less than a millimeter. Not too sure what you are up
to but that is close enough for me.
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

You will have to take it up with IEEE (Institute of Electrical and
Electronics Engineers). It is the international standard for floating point
arithmetic. XL complies with this standard. Once you have convinced them that
they are wrong I am sure the XL will fix your issue.
--
HTH...

Jim Thomlinson


"Jeff in GA" wrote:

This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?

Jim,

It must be great to be a monopolistic company that can foist defective
software on the public and then blame the standards for being inadequate.

Microsoft should make cars, and use the automotive standards from Bulgaria,
and then when people are injured in accidents they can blame Bulgaria for
having lousy standards.

....Jeff

"Jim Thomlinson" wrote:

You will have to take it up with IEEE (Institute of Electrical and
Electronics Engineers). It is the international standard for floating point
arithmetic. XL complies with this standard. Once you have convinced them that
they are wrong I am sure the XL will fix your issue.
--
HTH...

Jim Thomlinson


"Jeff in GA" wrote:

This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?

"Jeff in GA" wrote:
It must be great to be a monopolistic company that can foist defective
software on the public and then blame the standards for being inadequate.

It must feel great to make accusations out of ignorance before you even try
to understand the facts.

Perhaps Jim's comments misled you. There is nothing wrong with the IEEE
standards. It is simply a fact of life.

If you have 5 apples and you want to divide them among 6 people so that each
person gets a whole apple, it simply cannot be done.

Is this a defect of Mother Nature, and her standards are inadequate?

I will try to put your ignorant rants aside and explain the problem to you
elsewhere in this thread. But I seriously doubt that you are capable of or
even have the desire to truly understand.


----- original message -----

"Jeff in GA" wrote in message
...
Jim,

It must be great to be a monopolistic company that can foist defective
software on the public and then blame the standards for being inadequate.

Microsoft should make cars, and use the automotive standards from
Bulgaria,
and then when people are injured in accidents they can blame Bulgaria for
having lousy standards.

...Jeff

"Jim Thomlinson" wrote:

You will have to take it up with IEEE (Institute of Electrical and
Electronics Engineers). It is the international standard for floating
point
arithmetic. XL complies with this standard. Once you have convinced them
that
they are wrong I am sure the XL will fix your issue.
--
HTH...

Jim Thomlinson


"Jeff in GA" wrote:

This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel
returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product
quality.

Does Microsoft ever plan to repair its product, or should it only be
used
for calculating federal budget numbers?

"JoeU2004"

It must feel great to make accusations out of ignorance before you even try
to understand the facts.

Joe, I listen to politicians all the time and so I quickly recognize when
people are rationalizing the indefensible.

Meanwhile, .29 - .28 still equals .01, NOT 0.00999999999999995. Any rinky
dink calculator can accomplish that simple calculation, but NOT Excel. But
feel free to insult me instead of recognizing that reality.

Sorry. Whenever I encounter someone speaking out the wrong hole, I tend to
respond in kind with a brain fart of my own.


----- original message -----

"JoeU2004" wrote in message
...
"Jeff in GA" wrote:
It must be great to be a monopolistic company that can foist defective
software on the public and then blame the standards for being inadequate.

It must feel great to make accusations out of ignorance before you even
try to understand the facts.

Perhaps Jim's comments misled you. There is nothing wrong with the IEEE
standards. It is simply a fact of life.

If you have 5 apples and you want to divide them among 6 people so that
each person gets a whole apple, it simply cannot be done.

Is this a defect of Mother Nature, and her standards are inadequate?

I will try to put your ignorant rants aside and explain the problem to you
elsewhere in this thread. But I seriously doubt that you are capable of
or even have the desire to truly understand.


----- original message -----

"Jeff in GA" wrote in message
...
Jim,

It must be great to be a monopolistic company that can foist defective
software on the public and then blame the standards for being inadequate.

Microsoft should make cars, and use the automotive standards from
Bulgaria,
and then when people are injured in accidents they can blame Bulgaria for
having lousy standards.

...Jeff

"Jim Thomlinson" wrote:

You will have to take it up with IEEE (Institute of Electrical and
Electronics Engineers). It is the international standard for floating
point
arithmetic. XL complies with this standard. Once you have convinced them
that
they are wrong I am sure the XL will fix your issue.
--
HTH...

Jim Thomlinson


"Jeff in GA" wrote:

This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel
returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product
quality.

Does Microsoft ever plan to repair its product, or should it only be
used
for calculating federal budget numbers?

Seriously, this is funny! Jeff in GA is "mathematically challenged" and I
think we should enjoy his rants, not insult him. If he stops ranting, the
laughs will stop. BTW, here's one for you Jeff in GA, see if you can solve
it:

a = b

[multiply both sides by b]
ab = b^2

[subtract both sides from a^2]
a^2 - ab = a^2 - b^2

[factorise]
a(a - b) = (a + b)(a - b)

[delete common factor, (a-b)]
a = (a + b)

[since a=b, substitute]
a = (a + a)

[simplify]
a = 2a

[delete common factor, a]
1 = 2

I'm sure that will blow Jeff's fuse, but anyone else reading this might try
and find the kindergarten mathematical flaw with this bit of maths. Most
people struggle finding the flaw.

Jeff in GA, you're a riot! Keep it up :o)

Alain


"JoeU2004" wrote in message
...
Sorry. Whenever I encounter someone speaking out the wrong hole, I tend
to respond in kind with a brain fart of my own.


----- original message -----

"JoeU2004" wrote in message
...
"Jeff in GA" wrote:
It must be great to be a monopolistic company that can foist defective
software on the public and then blame the standards for being
inadequate.

It must feel great to make accusations out of ignorance before you even
try to understand the facts.

Perhaps Jim's comments misled you. There is nothing wrong with the IEEE
standards. It is simply a fact of life.

If you have 5 apples and you want to divide them among 6 people so that
each person gets a whole apple, it simply cannot be done.

Is this a defect of Mother Nature, and her standards are inadequate?

I will try to put your ignorant rants aside and explain the problem to
you elsewhere in this thread. But I seriously doubt that you are capable
of or even have the desire to truly understand.


----- original message -----

"Jeff in GA" wrote in message
...
Jim,

It must be great to be a monopolistic company that can foist defective
software on the public and then blame the standards for being
inadequate.

Microsoft should make cars, and use the automotive standards from
Bulgaria,
and then when people are injured in accidents they can blame Bulgaria
for
having lousy standards.

...Jeff

"Jim Thomlinson" wrote:

You will have to take it up with IEEE (Institute of Electrical and
Electronics Engineers). It is the international standard for floating
point
arithmetic. XL complies with this standard. Once you have convinced
them that
they are wrong I am sure the XL will fix your issue.
--
HTH...

Jim Thomlinson


"Jeff in GA" wrote:

This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel
returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product
quality.

Does Microsoft ever plan to repair its product, or should it only be
used
for calculating federal budget numbers?

Jeff,

You might want to examine your source numbers very closely, are you sure
that they only have 2 decimal places?

I tried your example and no matter how I extraploated it, I was unable to
get any answer BUT 0.01
The only way I could duplicate your answer was to add a bunch of decimal
places to the 2 source numbers.
--
Regards - Peter


"Jeff in GA" wrote:

This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?

Peter, 100% certain that they have only 2 decimal places. Duplicated it from
scratch on three different computers, and had others duplicate it. Be sure to
expand the decimal formatting of the result. Otherwise, it must be a
function of the version of the software you are using.

....Jeff

"Peter" wrote:

Jeff,

You might want to examine your source numbers very closely, are you sure
that they only have 2 decimal places?

I tried your example and no matter how I extraploated it, I was unable to
get any answer BUT 0.01
The only way I could duplicate your answer was to add a bunch of decimal
places to the 2 source numbers.
--
Regards - Peter


"Jeff in GA" wrote:

This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?

Okay, well I don't have any other computers but I forced the result to
display 60 decimal places and all I can see is
0.010000000000000000000000000000000 etc

I'm afraid I have no other suggestions.

BTW - I am using Excel 2003 SP3
--
Regards - Peter


"Jeff in GA" wrote:

Peter, 100% certain that they have only 2 decimal places. Duplicated it from
scratch on three different computers, and had others duplicate it. Be sure to
expand the decimal formatting of the result. Otherwise, it must be a
function of the version of the software you are using.

...Jeff

"Peter" wrote:

Jeff,

You might want to examine your source numbers very closely, are you sure
that they only have 2 decimal places?

I tried your example and no matter how I extraploated it, I was unable to
get any answer BUT 0.01
The only way I could duplicate your answer was to add a bunch of decimal
places to the 2 source numbers.
--
Regards - Peter


"Jeff in GA" wrote:

This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?

when i put .29 in A1 and .28 in B1 and =A1-B1 in C1, i get .01 until i get to 17
decimal places, then i get 0.00999999999999995. this happens in 2003 and 2007. i
have tried 2010 yet.

--


Gary Keramidas
Excel 2003


"Peter" wrote in message
...
Okay, well I don't have any other computers but I forced the result to
display 60 decimal places and all I can see is
0.010000000000000000000000000000000 etc

I'm afraid I have no other suggestions.

BTW - I am using Excel 2003 SP3
--
Regards - Peter


"Jeff in GA" wrote:

Peter, 100% certain that they have only 2 decimal places. Duplicated it from
scratch on three different computers, and had others duplicate it. Be sure to
expand the decimal formatting of the result. Otherwise, it must be a
function of the version of the software you are using.

...Jeff

"Peter" wrote:

Jeff,

You might want to examine your source numbers very closely, are you sure
that they only have 2 decimal places?

I tried your example and no matter how I extraploated it, I was unable to
get any answer BUT 0.01
The only way I could duplicate your answer was to add a bunch of decimal
places to the 2 source numbers.
--
Regards - Peter


"Jeff in GA" wrote:

This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product
quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?

Gary, the difference may seem trivial, but it shows up when you rely on
conditional formatting to match the values, and it returns a false condition.

Also, some of us (stupidly I suppose) rely on Excel for precise calculations
for small numbers. Now I see that Excel can't provide the accuracy of a
cheap calculator when it comes to some simple calculations.

But, hey, it's IEEE's fault! And I'm so stupid for pointing out that
Microsoft is reponsible for their products, not IEEE. You know, Microsoft has
so many really stupid customers, like me.

"Gary Keramidas" wrote:

when i put .29 in A1 and .28 in B1 and =A1-B1 in C1, i get .01 until i get to 17
decimal places, then i get 0.00999999999999995. this happens in 2003 and 2007. i
have tried 2010 yet.

Hello Jeff,

Read topic 8 of my Excel Dont's:
http://sulprobil.com/html/excel_don_ts.html

If you hate floating point arithmetic, try fix point one. You would
not love it, either.

Regards,
Bernd

Thanks for the constructive suggestion, given the peculiarity built into
Excel maybe that is the solution. It won't work with conditional formatting
however (at least not without adding additional steps).

What is strange about this problem (which as we all know is entirely IEEE's
fault) is that it doesn't happen with other nearby number pairs, like 0.30 -
0.29.

Those IEEE folks come up with some zany rules!!

....Jeff

"Bernd P" wrote:

Hello Jeff,

Read topic 8 of my Excel Dont's:
http://sulprobil.com/html/excel_don_ts.html

If you hate floating point arithmetic, try fix point one. You would
not love it, either.

Regards,
Bernd

"Jeff in GA" wrote:
This is: .29 - .28
Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

Excel does give you a tool for adjusting the result to your liking. Format
all cells with the number of desired decimal places, and set the calculation
option "Precision as displayed" (PAD). (In Excel 2003, click on Tools
Options Calculation.)

My preferred alternative is to use ROUND explicitly in most calculations.
This avoids some of the pitfalls of PAD.


Does Microsoft ever plan to repair its product

Ironically, it is the correctness of the calculation that is causing this
particular problem. I will explain the gory details below.


This floating point excuse for can not be blamed for poor product quality.

It is not "poor quality". It is one of several compromises that can be
made.

No matter what alternative you choose, you will always be faced with the
reality that there are no infinite resources in life. (At least, not until
we start building computer "bits" from individual atoms or "binary atoms".
Not truly infinite, but "uncountable".)

Some computers and some software add-ons provide "decimal arithmetic". That
helps to an extent, especially with simple arithmetic involving numbers with
a small number of non-repeating fractional digits. And I would agree: it
would be nice if Excel provide that as an option.

But even "decimal arithmetic" incurs problems with repeating decimal
fractions. For example, =1/3.

Some calculators, notably the MS Win calculator accessory, retain rational
numbers in their original form as a ratio of two integers, and they perform
some calculations by manipulating the ratios as we would on paper.

But even that strategy will fail as soon as we encounter a non-rational
number in the computation.

If you use a fixed-point or floating-point form with greater precision (more
bits), you are only deferring the problem. And there will still be a
potential problem with comparisons.

Excel does try to ameliorate such problems with an algorithm that attempts
to recognize and adjust "infinitesimal" differences. But the algorithm is
half-baked, leading to anomalies such as A1=A2 returns TRUE, but A1-A2=0
returns FALSE.

(Now for __that__ defect, I will join you in a chorus of complaints about
Microsoft's failure to correct flaws.)


Returning to your original problem....


This is: .29 - .28
Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This is easy to duplicate in Excel 2003. I don't know what Peter's problem
is.

The cause of the problem is: 0.29 is represented internally exactly as
0.289999999999999,98001598555674718227237462997436 5234375, and 0.28 is
exactly 0.280000000000000,02664535259100375697016716003417 96875 .

(The comma is my way of demarcating the first 15 significant digits, which
is all Excel will convert, rounding the 16th significant digit.)

Note that the representation of 0.29 is about 0.00000000000000002 (17
fractional digits) less than 0.29, and the representation of 0.28 about
0.00000000000000003 more than 0.28. So the difference is indeed
0.00000000000000005 less than 0.01.

The inexact representation of most decimal fractions is due to the fact that
binary floating point, the internal form, represents numbers by the sum of a
finite number of consecutive powers of 2. The operative word is "finite";
as noted above, there will always be some finite number digits.

Arguably, if 0.29 were represented by adding one more bit (2^-54), it would
be exactly 0.290000000000000,03552713678800500929355621337890 625, about
0.000000000000000035 (18 fractional digits). In that case, 0.29 - 0.28
would be 0.0100000000000000,0888178419700125232338905334472 65625, which
Excel would display as 0.010...0 to 15 significant digits. But ironically,
that representation of 0.29 is less accurate.


Elsewhere in the thread, you wrote:
What is strange about this problem (which as we all know is entirely
IEEE's
fault) is that it doesn't happen with other nearby number pairs, like
0.30 - 0.29.

Because 0.30 is represented internally exactly as
0.299999999999999,98889776975374843459576368331909 1796875. Note that is
less 0.30 by about the same amount that the representation of 0.29 is less
than 0.29. The difference is exactly
0.0100000000000000,0888178419700125232338905334472 65625.

If A1 is =0.30-029 and A2 is 0.01, =A1=A2 does result in TRUE. But A1 and
A2 are not truly equally, as evidenced by the fact that =A1-A2=0 results in
FALSE.

My point is: do not mislead yourself into thinking that any arithmetic with
decimal fractions is okay simply because you do not see the problem.

And just to reiterate, this is not "IEEE's fault". It is the fact that any
representation of decimal fractions in limited by finite resources, whether
that representation is binary or decimal. Arguably, decimal arithmetic
would mask some of the more flagrant examples.

I doubt that this will mollify your strong opinions. But I hope it gives
you a little insight and a modicum of understanding.


----- original message -----

"Jeff in GA" wrote in message
...
This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?

JoeU2004, that is a thoughtful reply and I do appreciate your earnest attempt
to explain this math problem which is percieved as intractable.

I'm willing to bet that academic-grade math software, which is used in
colleges and many commercial settings, (e.g., Mathematica), can provide
precise and correct answers to such problems (as .29 - .28). Given that, it
might be commercially viable for someone to create an add-in that would
enhance Excel's precision.

For prople looking for precise answers (e.g, where the orders of magnitude
are small) I wonder if it would make sense to de-decimalize the numbers,
compute, and then re-decimalize them.

....Jeff


"JoeU2004" wrote:

"Jeff in GA" wrote:
This is: .29 - .28
Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

Excel does give you a tool for adjusting the result to your liking. Format
all cells with the number of desired decimal places, and set the calculation
option "Precision as displayed" (PAD). (In Excel 2003, click on Tools
Options Calculation.)

I'm willing to bet that academic-grade math software, which is used in
colleges and many commercial settings, (e.g., Mathematica), can provide

Some software gives you better accuracy than does Excel. Some software
uses a larger data width that can provide more accuracy out farther to
the right. Also, some software can perform some mathematical
operations symbolically rather than numerically.

But even in the real world, rounding to some degree is necessary. No
matter how far out you carry out the calculations, you'll never get a
computationally correct answer to the addition 1/3 + 1/3 + 1/3 = 1.
No "academic grade" math software that doesn't use symbolic
manipulation will give you answer of 1. Take it out to 1000000 decimal
places and you'll still not equal to 1.

Professional computer programmers know the limitations of
computational arithmetic and write code to accommodate those
limitations. For example, when testing equality of floating point
numbers, code is not generally written as

If (X - Y) = 0 Then
' do something

Instead, code is written as

If Abs(X - Y) <= Epsilon Then
' do something

where Epsilon is some value, such as 0.00000001, scaled to the
compiler's representation of floating point numbers. Some programming
languages have this constant built in as a native element of the
language. In other languages you declare it yourself.

Greater accuracy in computed floating point numbers comes at a cost of
performance. It takes more operations to calculate a more accurate
representation of a quantity. At some point, the software must take an
approximation. Whether that approximation is at about 7 places in
Single Precision Floating Points, about at 15 places in Double
Precision Floating Points, or 1000000 places in some hypothetical
software, there will necessarily be some rounding. As long as you
are limited by a finite number of decimal places, rounding is
inevitable.

The designers of the software and programming languages take into
consideration the real world needs of the end users, the applications
built with the code and compiler, and the performance of the hardware
to decide how accurate the representation of the quantity needs to be.
For nearly all purposes, 15 digits of precision is adequate.

In order to make software and data consistent and sharable among
different systems, applications, and platforms, some standardized
format must be adhered to. For most software, that standard is the 8
byte Double Precision Floating Point standard published by IEEE. Is it
perfect? No. Is it the best possible standard? No. But it is what
nearly all software uses. Without some standard, you couldn't share
data between different programs. Would you leave it up to the user to
instruct the software to use 128 bits rather than 64? And then assume
that all users of the same data know to use 128 rather than 64? Can't
happen in the real world.

Software that uses the IEEE standard isn't "defective". The
limitations are known or should be known by the users and developers.
One could make the argument that the documentation is deficient by not
making clear the limitations of the software, but as long as the
standard is followed, the software does what it is designed to do.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Wed, 23 Sep 2009 03:06:02 -0700, Jeff in GA
wrote:

JoeU2004, that is a thoughtful reply and I do appreciate your earnest attempt
to explain this math problem which is percieved as intractable.

I'm willing to bet that academic-grade math software, which is used in
colleges and many commercial settings, (e.g., Mathematica), can provide
precise and correct answers to such problems (as .29 - .28). Given that, it
might be commercially viable for someone to create an add-in that would
enhance Excel's precision.

For prople looking for precise answers (e.g, where the orders of magnitude
are small) I wonder if it would make sense to de-decimalize the numbers,
compute, and then re-decimalize them.

...Jeff


"JoeU2004" wrote:

"Jeff in GA" wrote:
This is: .29 - .28
Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

Excel does give you a tool for adjusting the result to your liking. Format
all cells with the number of desired decimal places, and set the calculation
option "Precision as displayed" (PAD). (In Excel 2003, click on Tools
Options Calculation.)

Yes, you are quite correct, Jeff. If you are starting off with numbers with
a resolution of 0.01 (such as in many currency claculations), and if you
want precision, it would certainly make sense to multiply up and do the
calculation in cents (integers) rather than in dollars (floating point).
--
David Biddulph

"Jeff in GA" wrote in message
...
For prople looking for precise answers (e.g, where the orders of magnitude
are small) I wonder if it would make sense to de-decimalize the numbers,
compute, and then re-decimalize them.

Errata....

I wrote:
Arguably, if 0.29 were represented by adding one more bit (2^-54), it
would be exactly 0.290000000000000,03552713678800500929355621337890 625,
about 0.000000000000000035 (18 fractional digits). In that case, 0.29 -
0.28 would be 0.0100000000000000,0888178419700125232338905334472 65625,
which Excel would display as 0.010...0 to 15 significant digits.

I had decided not to correct the several typos in my posting, most of which
are not material. But I do want to correct a potential misimpression, even
though no one has (yet) made a point of it, just for the record.

First, what I meant to write is: if I increment the least significant bit
by adding 2^-54, it would have the result above, which is about
0.000000000000000035 (18 fractional digits) more than 0.29.

But I do not mean to suggest that Excel is flawed for not doing so in its
conversion.

The issue, again, has to do with binary floating point arithmetic.

The string "0.29" is converted to a number by computing 2/10 + 9/100. That
happens to result in exactly
0.289999999999999,98001598555674718227237462997436 5234375, which is less
than 0.29.

I had noted previously that the string "0.28" happens to convert, by
computing 2/10 + 8/100, to the exact number
0.280000000000000,02664535259100375697016716003417 96875, which is more than
0.28.

The different directions in conversion -- one less, the other more -- is
partly responsible for the failure of the simplistic case of 0.29 - 0.28 not
appearing to be 0.01 when formatted to 16 decimal places (15 significant
digits).

That might raise the speculation that Excel should always convert to a
binary value greater than (if not equal to) the original numeric string.

But for this example, it is not a panacea.

Even though the result of 0.29 - 0.28 (in A3, say) might appear to be 0.01
when formatted to 16 decimal places, the formula =A3=0.01 returns FALSE (!).

The reason is: the exact result of (0.29 + 2^-54) - 0.28 is
0.0100000000000000,0888178419700125232338905334472 65625. That is different
enough from the internal representation of the constant 0.01 for Excel to
recognize the difference, despite its heuristics to adjust infinitesimal
differences. The exact internal representation of the constant 0.01 is
0.0100000000000000,0020816681711721685132943093776 702880859375.

Moreover, one might reasonably question the wisdom of trying to make this
specific example work better.

The reason is: there are 18 valid representations of each of 0.29 and 0.28,
any of which might be the result of an arithmetic expression. The 324
combinations of subtracting 0.28 from 0.29 result in any of 35 values from
about 0.00999999999999907 to about 0.01000000000000095. Only 18
combinations result in what appears to be 0.01 when formatted to 16 decimal
places. But =A3=0.01 returns FALSE for all of those combinations, since all
18 are exactly 0.0100000000000000,0888178419700125232338905334472 65625.


----- original message -----

"JoeU2004" wrote in message
...
"Jeff in GA" wrote:
This is: .29 - .28
Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

Excel does give you a tool for adjusting the result to your liking.
Format all cells with the number of desired decimal places, and set the
calculation option "Precision as displayed" (PAD). (In Excel 2003, click
on Tools Options Calculation.)

My preferred alternative is to use ROUND explicitly in most calculations.
This avoids some of the pitfalls of PAD.


Does Microsoft ever plan to repair its product

Ironically, it is the correctness of the calculation that is causing this
particular problem. I will explain the gory details below.


This floating point excuse for can not be blamed for poor product
quality.

It is not "poor quality". It is one of several compromises that can be
made.

No matter what alternative you choose, you will always be faced with the
reality that there are no infinite resources in life. (At least, not
until we start building computer "bits" from individual atoms or "binary
atoms". Not truly infinite, but "uncountable".)

Some computers and some software add-ons provide "decimal arithmetic".
That helps to an extent, especially with simple arithmetic involving
numbers with a small number of non-repeating fractional digits. And I
would agree: it would be nice if Excel provide that as an option.

But even "decimal arithmetic" incurs problems with repeating decimal
fractions. For example, =1/3.

Some calculators, notably the MS Win calculator accessory, retain rational
numbers in their original form as a ratio of two integers, and they
perform some calculations by manipulating the ratios as we would on paper.

But even that strategy will fail as soon as we encounter a non-rational
number in the computation.

If you use a fixed-point or floating-point form with greater precision
(more bits), you are only deferring the problem. And there will still be
a potential problem with comparisons.

Excel does try to ameliorate such problems with an algorithm that attempts
to recognize and adjust "infinitesimal" differences. But the algorithm is
half-baked, leading to anomalies such as A1=A2 returns TRUE, but A1-A2=0
returns FALSE.

(Now for __that__ defect, I will join you in a chorus of complaints about
Microsoft's failure to correct flaws.)


Returning to your original problem....


This is: .29 - .28
Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This is easy to duplicate in Excel 2003. I don't know what Peter's
problem is.

The cause of the problem is: 0.29 is represented internally exactly as
0.289999999999999,98001598555674718227237462997436 5234375, and 0.28 is
exactly 0.280000000000000,02664535259100375697016716003417 96875 .

(The comma is my way of demarcating the first 15 significant digits, which
is all Excel will convert, rounding the 16th significant digit.)

Note that the representation of 0.29 is about 0.00000000000000002 (17
fractional digits) less than 0.29, and the representation of 0.28 about
0.00000000000000003 more than 0.28. So the difference is indeed
0.00000000000000005 less than 0.01.

The inexact representation of most decimal fractions is due to the fact
that binary floating point, the internal form, represents numbers by the
sum of a finite number of consecutive powers of 2. The operative word is
"finite"; as noted above, there will always be some finite number digits.

Arguably, if 0.29 were represented by adding one more bit (2^-54), it
would be exactly 0.290000000000000,03552713678800500929355621337890 625,
about 0.000000000000000035 (18 fractional digits). In that case, 0.29 -
0.28 would be 0.0100000000000000,0888178419700125232338905334472 65625,
which Excel would display as 0.010...0 to 15 significant digits. But
ironically, that representation of 0.29 is less accurate.


Elsewhere in the thread, you wrote:
What is strange about this problem (which as we all know is entirely
IEEE's
fault) is that it doesn't happen with other nearby number pairs, like
0.30 - 0.29.

Because 0.30 is represented internally exactly as
0.299999999999999,98889776975374843459576368331909 1796875. Note that is
less 0.30 by about the same amount that the representation of 0.29 is less
than 0.29. The difference is exactly
0.0100000000000000,0888178419700125232338905334472 65625.

If A1 is =0.30-029 and A2 is 0.01, =A1=A2 does result in TRUE. But A1 and
A2 are not truly equally, as evidenced by the fact that =A1-A2=0 results
in FALSE.

My point is: do not mislead yourself into thinking that any arithmetic
with decimal fractions is okay simply because you do not see the problem.

And just to reiterate, this is not "IEEE's fault". It is the fact that
any representation of decimal fractions in limited by finite resources,
whether that representation is binary or decimal. Arguably, decimal
arithmetic would mask some of the more flagrant examples.

I doubt that this will mollify your strong opinions. But I hope it gives
you a little insight and a modicum of understanding.


----- original message -----

"Jeff in GA" wrote in message
...
This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product
quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?

Perhaps you could tell us what the exact binary representation of 0.29 would
be?
--
David Biddulph

"Jeff in GA" wrote in message
...
This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?

David, I appreciate your commitment to the notion that there could not
possibly be a solution to the problem, and that we should all accept
incorrect mathematical results.

Meanwhile, even my fourth grade child knows that .30 - .29 = .0100000000000

But yes, David, let's adapt to the machines, and not make the machines adapt
to us.

Also: the customer is always wrong.

Happy now?

....Jeff

"David Biddulph" wrote:

Perhaps you could tell us what the exact binary representation of 0.29 would
be?
--
David Biddulph

"Jeff in GA" wrote in message
...
This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?

Jeff, as you have no doubt realised, the IEEE is a secretive, subversive
organisation with no known headquarters (they keep on the move to escape the
authorities). Why did Microsoft ever collude with these evildoers???

Hopefully Microsoft will start to see the error in their ways.

I demand action!

"Jeff in GA" wrote in message
...
David, I appreciate your commitment to the notion that there could not
possibly be a solution to the problem, and that we should all accept
incorrect mathematical results.

Meanwhile, even my fourth grade child knows that .30 - .29 =
.0100000000000

But yes, David, let's adapt to the machines, and not make the machines
adapt
to us.

Also: the customer is always wrong.

Happy now?

...Jeff

"David Biddulph" wrote:

Perhaps you could tell us what the exact binary representation of 0.29
would
be?
--
David Biddulph

"Jeff in GA" wrote in message
...
This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel
returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product
quality.

Does Microsoft ever plan to repair its product, or should it only be
used
for calculating federal budget numbers?

We know what the answer should be, and we also know the limitations of a
fixed point binary machine with a number such as 1/10, just as we know the
limitations of a fixed point decimal machine for a number such as 1/3, and
we treat the results accordingly.

You are welcome to use whichever machine and whichever software you prefer.
If you prefer to use something other than Excel, you are welcome to do so.
If you want to use something that works in decimal rather than binary, you
are welcome to do so. If you wish to write some software of your own to
replace Excel, then again you are welcome to do so.
--
David Biddulph

"Jeff in GA" wrote in message
...
David, I appreciate your commitment to the notion that there could not
possibly be a solution to the problem, and that we should all accept
incorrect mathematical results.

Meanwhile, even my fourth grade child knows that .30 - .29 =
.0100000000000

But yes, David, let's adapt to the machines, and not make the machines
adapt
to us.

Also: the customer is always wrong.

Happy now?

...Jeff

"David Biddulph" wrote:

Perhaps you could tell us what the exact binary representation of 0.29
would
be?
--
David Biddulph

"Jeff in GA" wrote in message
...
This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel
returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product
quality.

Does Microsoft ever plan to repair its product, or should it only be
used
for calculating federal budget numbers?

Jeff
You can't have a greater degree of accuracy in a result than was present
in the arguments
..30 - .29 does = .01 to 2 significant digits, beyond that the result is
undefined. .01000000000 is just one of many valid ones

..01 is the 2sd representation of any number from .005 to less than .015.

If you have a need for such high precision then you probably need to use
specialist tools rather than a general purpose product like Excel.

cheers
Simon

Jeff in GA wrote:
David, I appreciate your commitment to the notion that there could not
possibly be a solution to the problem, and that we should all accept
incorrect mathematical results.

Meanwhile, even my fourth grade child knows that .30 - .29 = .0100000000000

But yes, David, let's adapt to the machines, and not make the machines adapt
to us.

Also: the customer is always wrong.

Happy now?

...Jeff

"David Biddulph" wrote:

Perhaps you could tell us what the exact binary representation of 0.29 would
be?
--
David Biddulph

"Jeff in GA" wrote in message
...
This is NOT some calculation to the nth place or an atomic weight.

This is: .29 - .28

Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995

This floating point excuse for can not be blamed for poor product quality.

Does Microsoft ever plan to repair its product, or should it only be used
for calculating federal budget numbers?