Unacceptable floating point errors

  • Thread starter Thread starter Jeff in GA
  • Start date Start date
J

Jeff in GA

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.
 
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.
 
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.
 
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
 
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
 
Jeff in GA said:
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.
Click to expand...

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 -----
 
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
 
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.
 
"JoeU2004"
It must feel great to make accusations out of ignorance before you even try
to understand the facts.
Click to expand...

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 -----
 
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.
 
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
 
Jeff in GA said:
This is: .29 - .28
Instead of returning the correct answer of 0.01, Microsoft Excel returns
0.00999999999999995
Click to expand...

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
Click to expand...

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.
Click to expand...

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
Click to expand...

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,980015985556747182272374629974365234375, and 0.28 is
exactly 0.280000000000000,0266453525910037569701671600341796875 .

(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,03552713678800500929355621337890625, about
0.000000000000000035 (18 fractional digits). In that case, 0.29 - 0.28
would be 0.0100000000000000,088817841970012523233890533447265625, which
Excel would display as 0.010...0 to 15 significant digits. But ironically,
that representation of 0.29 is less accurate.


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.
Click to expand...

Because 0.30 is represented internally exactly as
0.299999999999999,988897769753748434595763683319091796875. 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,088817841970012523233890533447265625.

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 -----
 
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
 
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
 
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
 
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!
 
Back
Top