Strange Calculation Error in Excel

Hi.
I have a table of numbers, and I do some simple Math based on this data.
Eg:
4.1 4.1 3.97 4.03
4.15 4.15 4.1 4.1


For the formula "=A4-B4", the answer given is
"-0.069999999999999400000000000000"
It should be just -0.07.

Then I checked the value for A4 & B4, the values a
4.030000000000000000000000000000
4.100000000000000000000000000000

What's wrong with Excel?
How can I correct this error?
PS: If anyone wishes to see the original Excel file, please leave your email
address here. Thanks!

--
Additional information:
- I'm using Office XP
- I'm using Windows XP

¥»¤Hªº¯à¤O«D±`¦³**. ¦p¦³¤£·í¤§³B, ±æÃѪ̤£§[½ç¥¿!!
My ability is very limited. Hope you will not mind to enlighten me if I do
wrongly.

You can't correct the error. It's how computers have worked since day one. It
happens because computers convert from decimal to binary, and not all
conversions are perfect.

To avoid to problem, use the Round function, as in

=Round(a4-b4,2)

--
Regards,
Fred


"0-0 Wai Wai ^-^" wrote in message
...

Hi.
I have a table of numbers, and I do some simple Math based on this data.
Eg:
4.1 4.1 3.97 4.03
4.15 4.15 4.1 4.1


For the formula "=A4-B4", the answer given is
"-0.069999999999999400000000000000"
It should be just -0.07.

Then I checked the value for A4 & B4, the values a
4.030000000000000000000000000000
4.100000000000000000000000000000

What's wrong with Excel?
How can I correct this error?
PS: If anyone wishes to see the original Excel file, please leave your email
address here. Thanks!

--
Additional information:
- I'm using Office XP
- I'm using Windows XP

¥»¤Hªº¯à¤O«D±`¦³**. ¦p¦³¤£·í¤§³B, ±æÃѪ̤£§[½ç¥¿!!
My ability is very limited. Hope you will not mind to enlighten me if I do
wrongly.

"Fred Smith" ¦b¶l¥ó
¤¤¼¶¼g...
You can't correct the error. It's how computers have worked since day one. It
happens because computers convert from decimal to binary, and not all
conversions are perfect.

To avoid to problem, use the Round function, as in

=Round(a4-b4,2)

Thanks for your reply.
But the solution is not practical since I need to do so for everything I
calculate in Excel.

Take the above example again:
C4: =A4-B4
D4: =countif(C4, -0.07)

The answer is 0. Too bad! It should be 1.
Your answer did eliminate the problem, but I have many different formulas which
are baffled by this strange calculation error.
It is tons of rewriting. Oh no!!

Is there any practical solution I would take to workaround this "calcualtion
error"?
Thanks!

"0-0 Wai Wai ^-^" wrote:
"Fred Smith" :
To avoid to problem, use the Round function, as in
=Round(a4-b4,2)

Thanks for your reply. But the solution is not practical
since I need to do so for everything I calculate in Excel.
[....]
I have many different formulas which are baffled by this
strange calculation error. It is tons of rewriting. Oh no!!

Try setting Tools Options Calculation Precision As
Displayed.

Of course, then you might need to adjust the format of
some cells to be sure that they display the needed precision.
This includes "helper" cells, which you might have hidden.

Caveat emptor.

" ¦b¶l¥ó
¤¤¼¶¼g...
"0-0 Wai Wai ^-^" wrote:
"Fred Smith" :
To avoid to problem, use the Round function, as in
=Round(a4-b4,2)

Thanks for your reply. But the solution is not practical
since I need to do so for everything I calculate in Excel.
[....]
I have many different formulas which are baffled by this
strange calculation error. It is tons of rewriting. Oh no!!

Try setting Tools Options Calculation Precision As
Displayed.

Of course, then you might need to adjust the format of
some cells to be sure that they display the needed precision.
This includes "helper" cells, which you might have hidden.

Caveat emptor.

Hi.
Is it possible to set something like it is displayed up to 2 decimal places, but
the precision holds up to 10 decimal places or so?

I don't wish to show all figures up to 10 decminal places. It is just too
clumsy.
And since this setting is global, it is still not preferable.

Hi Wai Wai,

You might want to try my Excel add-in, xlPrecision 2.0. It avoids these
errors by never converting to binary.

You can download the free edition of xlPrecision 2.0 from here and use
it as long as you wish:

http://PrecisionCalc.com


Good Luck,

Greg Lovern
http://PrecisionCalc.com
Eliminate Hidden Spreadsheet Errors

You might want to try my Excel add-in, xlPrecision 2.0.
It avoids these errors by never converting to binary.
[....]
Greg Lovern
http://PrecisionCalc.com
Eliminate Hidden Spreadsheet Errors

Fascinating! I would imagine it slows computation
tremendously. Do you have any performance comparisons?

Would be better if Excel itself implemented BCD, at least
as an option. With nano-to-picosec instruction times, it is
probably a reasonable trade-off. I hope Bill is listening :-).

Hi Joe,

I would imagine it slows computation tremendously. Do you have any performance comparisons?

I haven't benchmarked it against rounding in Excel, but obviously it's
slower than Excel. xlPrecision's main purpose is high precision (i.e.,
more than 15 significant digits), and one reasonably expects to
sacrifice performance for that. Avoiding binary conversion errors is a
happy side effect.

I heard praise, and no complaints, for version 1.0's performance (for
high precision), and 2.0 is faster. And the next version will be faster
still.


Thanks,

Greg Lovern
http://PrecisionCalc.com
Eliminate Hidden Spreadsheet Errors

0-0 Wai Wai ^-^ wrote:

It doesn't really matter how the number is displayed after, say, 15 decimal
points.
But what I want is it can still be calculated without being affected by this
minor mistake.


Calling it a "mistake" suggests that you still do not understand. It is
an inevitable consequence of finite precision mathematics. Suppose you
were doing decimal math with 4-figure precision. Then (4+1/3)-(4+2/3)
would be
4.333-4.667 = -0.334
There is no mistake, but the result is numerically different from the
representation of 1/3 = 0.3333 in this system.

You are presumably aware that numbers like 1/3, 1/7, 1/11, etc. cannot
be exactly represented in decimal, and so you are not surprised when
numbers like these have to be approximated. The only additional
surprise here is that numbers like 1/5 are also non-terminating binary
fractions, with the result that most finite decimal fractions (including
..03, .07, .1, .15, and .97) can only be approximated. When you do math
with approximate inputs, you should not be surprised when the result is
also an approximation. It is not an "error", "mistake", "imperfect
conversion", etc. it is just the nature of the beast.

Converting to BCD as joeu2004 suggested would not eliminate the problem,
as my decimal example illustrated. It would just confine the problem
(finite precision approximation to numbers that can only be exactly
represented in infinite precision) to numbers where we more readily
recognize what has happened. BCD is rarely done in computers, because
it is relatively wasteful and slow, which seems a steep price to pay for
a "solution" that doesn't fully solve the problem.

Extended precision packages like xlPrecision also do not solve the
problem, they just push it farther out (though they do have their uses).
The only way to completely solve the problem is to do symbolic math
http://en.wikipedia.org/wiki/Compute...ebra_s ystems

But the performance penalty from that option would be totally
unacceptable for large spreadsheets.


Just like the countif function. It can't calculate well due to the small
difference of 0.00....005
Any workaround is appreciated.


If you are unwilling to standardize the approximations (using ROUND() on
the calculations or setting the Precision as Displayed option), then you
need to do comparisons that are robust to approximations. Examples
would include IF(ROUND(C4,2)=0.07,... or IF(ABS(C4-0.07)0.005,...
For summarizing a range, this would generally require array formulas.

Jerry

"Jerry W. Lewis" ???
???...
0-0 Wai Wai ^-^ wrote:

It doesn't really matter how the number is displayed after, say, 15 decimal
points.
But what I want is it can still be calculated without being affected by this
minor mistake.


Calling it a "mistake" suggests that you still do not understand. It is
an inevitable consequence of finite precision mathematics.

Thanks for your explanation.
I did know a bit after the first reply.
At that time, I searched for information about this problem.

Just a thought to me. Since it is an inevitable consequence is notihng to do to
say whether it is not a mistake. Humans always make mistakes. It is an
inevitable consequence in our life. But does that mean they are no longer
mistakes then since they are inevitable?
Inevitablity is nothing to do with classifying a mistake.

Anyway, this correspondence is just a casual one. Thus not every word is
carefully thought out before written. Maybe "this sort of problem" should not be
called mistake since it seems I am blaming my computer without any appreciation
of its limitation. Maybe "error", or "natural beast" is a better name for "this
problem".


Suppose you
were doing decimal math with 4-figure precision. Then (4+1/3)-(4+2/3)
would be
4.333-4.667 = -0.334
There is no mistake, but the result is numerically different from the
representation of 1/3 = 0.3333 in this system.

You are presumably aware that numbers like 1/3, 1/7, 1/11, etc. cannot
be exactly represented in decimal, and so you are not surprised when
numbers like these have to be approximated. The only additional
surprise here is that numbers like 1/5 are also non-terminating binary
fractions, with the result that most finite decimal fractions (including
.03, .07, .1, .15, and .97) can only be approximated. When you do math
with approximate inputs, you should not be surprised when the result is
also an approximation. It is not an "error", "mistake", "imperfect
conversion", etc. it is just the nature of the beast.

Converting to BCD as joeu2004 suggested would not eliminate the problem,
as my decimal example illustrated. It would just confine the problem
(finite precision approximation to numbers that can only be exactly
represented in infinite precision) to numbers where we more readily
recognize what has happened. BCD is rarely done in computers, because
it is relatively wasteful and slow, which seems a steep price to pay for
a "solution" that doesn't fully solve the problem.

Extended precision packages like xlPrecision also do not solve the
problem, they just push it farther out (though they do have their uses).
The only way to completely solve the problem is to do symbolic math

http://en.wikipedia.org/wiki/Compute...ebra_s ystems

But the performance penalty from that option would be totally
unacceptable for large spreadsheets.


Just like the countif function. It can't calculate well due to the small
difference of 0.00....005
Any workaround is appreciated.


If you are unwilling to standardize the approximations (using ROUND() on
the calculations or setting the Precision as Displayed option), then you
need to do comparisons that are robust to approximations. Examples
would include IF(ROUND(C4,2)=0.07,... or IF(ABS(C4-0.07)0.005,...
For summarizing a range, this would generally require array formulas.

I'm willing to use round(), but there are tons of rewriting.
It seems to be impossible to rewrite all of them by human.
It would be great if you could suggest a method which can rewrite 1000 formulas
automatically.

As to "Precision as Displayed", it is a bad idea since I will either sacrifice
precision or force me to display 10-decimal-point for every figure (clumsy
looking

Anyway, I just wonder why countif won't work under its "binary-to-decimal"
problem.
If, say, computers can only store 0.06999999...9994 for 0.07, so when I type
0.07, computers should actually treat it as 0.069999999...9994 (since it can't
store 0.07 precisely).

Hmm... I know I am probably asking stupid questions.
But when I type countif(A1,0.07), it won't count it.
What does it imply?
Doesn't it mean computers can still store 0.07?
To computers, 0.07 or 10-9.93 should mean the same as computers, ie
0.06999...9994. But from the result, it seems computer read the first one as
0.07, the second as 0.06999...9994.
OK, I'm going idiotic. X(