EXCEL IF FUNCTION

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Excel IF Function Help!!! plz?

Ok I am having problem with Excel IF Function.

Here is the problem.

(Cell A1) 10209.0 - (Cell A2) 10208.7
(Cell A5) = A1 - A2

So (Cell A5) i have the answer of 0.3

Then on (Cell A7) i put IF Function

=IF(A5=0.3,2,0) I put if A5 is equal 0.3 then give me 3 or else 0

But the answer Excel gave me is "0"
WHY ???? A5 IS = 0.3

WHY ?? HELP Plz....

Thanks
 
It looks like this is a bug, if you substitute the values you have used with
1 and 0.7 respectively, the formula works as expected.

MH
 
MH said:
It looks like this is a bug
Click to expand...

It is not a bug, it is the result of the approximation that all computers
using binary representation of fractions makes.

=10209-10208.7
results in 0.299999999999272 although it displays as 0.3

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Well if it is a bug then it is a bug in every computer using IEEE Standard
for Binary Floating Point Arithmetic see:

http://en.wikipedia.org/wiki/IEEE_754

--
HTH

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
You have missed the point.

If you had a hypothetical computer that carried four decimal figures, then
1/3 would be represented as 0.3333, and 100+1/3 would be represented as
100.3. You would correctly get different answers for 1-1/3=0.6667 and
101-(100+1/3)=0.7. It is the consistent, correct, and well understood
consequence of finite precision arithmetic. The same thing happens here,
with the additional wrinkle that the math is binary, so that non-terminating
fractions that can only be approximated occur were you may not be expecting
them. Indeed, most terminating decimal fractions are non-terminating binary
fractions.

The binary approximation to 10208.7 has a decimal value of
10208.70000000000072759576141834259033203125, so Excel correctly calculates
10209-10208.7 to be 0.29999999999927240423858165740966796875. This differs
in the 13th figure from the binary approximation to 0.3
(0,299999999999999988897769753748434595763683319091796875), so Excel
correctly reports a difference.

The binary approximation to 0.7 has a decimal value of
0.6999999999999999555910790149937383830547332763671875, so Excel correctly
calculates 1-0.7 to be
0.3000000000000000444089209850062616169452667236328125. This differs from
the binary approximation to 0.3 in the 17th figure, so IF() glosses over the
difference since Excel does not display more than 15 figures (documented in
Help). You can detect the actual values by subtraction, as in
=(1-0.7-0.3)
The parentheses are required in this formula to keep Excel from fuzzing away
the small difference.

Almost all software on almost all hardware would use exactly the same binary
approximations that Excel does (IEEE double precision). Where Excel differs
from many of those packages is in limiting display and default comparisons to
15 decimal figures to try to gloss over the limitations of finite precision
arithmetic. The problem with glossing over them is that it is not possible
to make it go away entirely and depending on the particular calculations
involved, the level of fuzzing may be not enough (as in this case) or too
much. Meanwhile the fuzz factor makes it more difficult to recognize what is
really going on. IMHO a better approach would be teach users to use proper
floating point programming techniques.

Jerry
 
Thanks Jerry, that cleared that up.

MH

Jerry W. Lewis said:
You have missed the point.

If you had a hypothetical computer that carried four decimal figures, then
1/3 would be represented as 0.3333, and 100+1/3 would be represented as
100.3. You would correctly get different answers for 1-1/3=0.6667 and
101-(100+1/3)=0.7. It is the consistent, correct, and well understood
consequence of finite precision arithmetic. The same thing happens here,
with the additional wrinkle that the math is binary, so that
non-terminating
fractions that can only be approximated occur were you may not be
expecting
them. Indeed, most terminating decimal fractions are non-terminating
binary
fractions.

The binary approximation to 10208.7 has a decimal value of
10208.70000000000072759576141834259033203125, so Excel correctly
calculates
10209-10208.7 to be 0.29999999999927240423858165740966796875. This
differs
in the 13th figure from the binary approximation to 0.3
(0,299999999999999988897769753748434595763683319091796875), so Excel
correctly reports a difference.

The binary approximation to 0.7 has a decimal value of
0.6999999999999999555910790149937383830547332763671875, so Excel correctly
calculates 1-0.7 to be
0.3000000000000000444089209850062616169452667236328125. This differs from
the binary approximation to 0.3 in the 17th figure, so IF() glosses over
the
difference since Excel does not display more than 15 figures (documented
in
Help). You can detect the actual values by subtraction, as in
=(1-0.7-0.3)
The parentheses are required in this formula to keep Excel from fuzzing
away
the small difference.

Almost all software on almost all hardware would use exactly the same
binary
approximations that Excel does (IEEE double precision). Where Excel
differs
from many of those packages is in limiting display and default comparisons
to
15 decimal figures to try to gloss over the limitations of finite
precision
arithmetic. The problem with glossing over them is that it is not
possible
to make it go away entirely and depending on the particular calculations
involved, the level of fuzzing may be not enough (as in this case) or too
much. Meanwhile the fuzz factor makes it more difficult to recognize what
is
really going on. IMHO a better approach would be teach users to use
proper
floating point programming techniques.

Jerry
Click to expand...
 
Back
Top