EXCEL IF FUNCTION

[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

 

[Guest]

=IF(ROUND(A5,2)=0.3,3,0)

 

[MH]

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

 

[Sandy Mann]

It looks like this is a bug

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

 

[MH]

If that were the case then the same error would occur for the values of 1
and 0.7

MH

 

[Guest]

Thank you everyone helps. It help me A LOT.

Thank you again

-Ah Ping

 

[JE McGimpsey]

You can't make that analogy, since that's not how double precision
floating point values are stored. Your comparison is in decimal, not
binary. Take a look at

http://cpearson.com/excel/rounding.htm

for a good basic explanation of the IEEE standard.

 

[Sandy Mann]

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

 

[Guest]

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

 

[MH]

Thanks Jerry, that cleared that up.

MH

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