Error in Excel Calculations?

[BEETHOVEN]

A1 is 0.69
A2 is 0.64
A3 is =A1-A2
A4 is 0.05
A5 is =IF(A4=A3,"Yes","No")


In A5 you should get "Yes" but I get "No". I get "No" for the
following as well in A1 and A2:
A1 is 0.70 A1 is 0.71 A1 is 0.72 A1 is 0.73
A2 is 0.65 A2 is 0.66 A2 is 0.67 A2 is 0.68

I get "Yes" for the following in A1 and A2:
A1 is 0.67 A1 is 0.68 A1 is 0.74 A1 is 0.75
A2 is 0.62 A2 is 0.63 A2 is 0.69 A2 is 0.70

If you take the number in A3 to 16 decimal places or more you can see
that 0.05 becomes 0.0499999999999999 which which makes the formula in
A5 to state "No".

My best response from our Help Desk was:
This is occurring because of the way the numbers are calculated
between binary and decimal. This is a programming issue with Excel,
aside from notifying Microsoft about this bug in order for them to
include it in an update, there is nothing that I can do.

Thanks for any help
Gary 11-14-2007

 

[Sandy Mann]

ROUND() A3 before comparing it:

=IF(A4=ROUND(A3,2),"Yes","No")

If I re-format A3 as Number with 16 decimalplaces I get:

0.0499999999999999

Thus your problem.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Gord Dibben]

Your help desk is correct.

One workaround is to format to 2 DP then set Tools>Options>Calculation to
"Precision as displayed".

Watch out for this.......it is a workbook setting so would affect all cells in
all sheets in the workbook.


Gord Dibben MS Excel MVP

 

[Pete_UK]

Well, what help do you want? The vast majority of computers use binary
to represent numbers, and many decimal fractions cannot be expressed
with complete accuracy in binary. Hence small errors can occur, which
can accumulate as you carry out arithmetic. Your "bug" is just making
use of these errors.

Imagine you can only represent decimal fractions to 4 places. Then 1/3
would be 0.3333, and if you add this up three times you get 0.9999,
yet we know that 3 times 1/3 equals 1, so there is an error here when
using decimals. The same problem occurs with binary fractions.

You can use the ROUND function to help alleviate the problems.

Hope this helps.

Pete

 

[Bob Phillips]

Try this Gary

=IF(ABS(A4-A3)<1/10^6,"Yes","No")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jerry W. Lewis]

The calculations are exactly correct, given the unavoidable approximations to
the numbers you are using. As your Help Desk and other respondents noted,
Excel uses binary representation for numbers. Most decimal fractions have no
exact finite binary representations, and hence must be approximated. Excel
uses IEEE standard double precision, which is the same approximation used by
almost all software. Excel's help indicates that Excel has 15 digit
accuracy, because double precision may not accurately represent the 16th
figure.

Consequently, a simple way to think about your calculation is
0.690000000000000???
-0.640000000000000???
-----------------------------
0.050000000000000???
which is entirely consistent with Excel's answer of
0.0499999999999999

Excel will not natively display more than 15 figures, but you can use the
VBA utilities a
http://groups.google.com/group/micr..._frm/thread/9f83ca3dea38e501/6efb95785d1eaff5
to see what is actually going on
0.689999999999999946709294817992486059665679931640625
0.64000000000000001332267629550187848508358001708984375
------------------------------------------------------------------------------
-0.04999999999999993338661852249060757458209991455078125
which is correct arithmetic, and the result is different than the binary
approximation to 0.05
5.000000000000000277555756156289135105907917022705078125E-2
so Excel was also correct to return "No" in your formula.

Since the issue is approximations to the numbers themselves, and there are
no math errors involved, you can safely round results to an appropriate level
for comparisons. Alternately, you can do comparisons by asking if the
absolute difference is appropriately small (a method that has been taught in
programming classes for decades longer than Microsoft has existed).

Jerry