Strange Calculation Error in Excel

[0-0 Wai Wai ^-^]

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 are:
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]

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 ^-^" <x@x.com> wrote in message
news:erj2rf59FHA.3928@TK2MSFTNGP11.phx.gbl...
>
> 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 are:
> 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.
>
>

[0-0 Wai Wai ^-^]

"Fred Smith" <fredsmith99@yahoo.com> ¦b¶l¥ó
news:OxJl%23i59FHA.912@TK2MSFTNGP11.phx.gbl ¤¤¼¶¼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!

[joeu2004@hotmail.com]

"0-0 Wai Wai ^-^" wrote:
> "Fred Smith" <fredsmith99@yahoo.com>:
> > 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.

[0-0 Wai Wai ^-^]

"joeu2004@hotmail.com" <joeu2004hotmailcom@discussions.microsoft.com> ¦b¶l¥ó
news:5F6C778F-2FC4-490C-A4AC-81F65A1F4687@microsoft.com ¤¤¼¶¼g...
> "0-0 Wai Wai ^-^" wrote:
> > "Fred Smith" <fredsmith99@yahoo.com>:
> > > 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.

[gregl@gregl.net]

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

[joeu2004@hotmail.com]

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

[gregl@gregl.net]

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

[kaream@nmia.com]

Hi Fred --

I understand the reason why decimals will not always convert perfectly
to binary, but the similar problem that I kept running into (primarily
using Lotus 1-2-3 Ver 1A -- I've only recently had to start working in
Excel, and so far have had only one occasion to do a work-around in
that program) is that I would get floating point errors when adding
WHOLE NUMBERS that mathematically should sum to zero. Surely whole
numbers should convert perfectly to binary. I frequently needed to
test whether a sum equaled zero, but the best fix I could come up with
was "if(abs([formula])<0.001,[do A],[do B]).

Why adding and subtracting whole numbers would yield these same 15th
decimal place discrepancies is completely beyond my comprehension.