ExcelBanter - EXCEL IF FUNCTION

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - EXCEL IF FUNCTION (https://www.excelbanter.com/excel-worksheet-functions/140851-excel-if-function.html)

EXCEL IF FUNCTION

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

EXCEL IF FUNCTION

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

"Ah Ping" wrote:

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

EXCEL IF FUNCTION

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

MH

"Sandy Mann" wrote in message
...
"MH" wrote in message
...
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

with @tiscali.co.uk

"MH" wrote in message
...
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

"Ah Ping" <Ah wrote in message
...
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

EXCEL IF FUNCTION

Thank you everyone helps. It help me A LOT.

Thank you again

-Ah Ping

EXCEL IF FUNCTION

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.

In article ,
"MH" wrote:
If that were the case then the same error would occur for the values of 1
and 0.7

MH

"Sandy Mann" wrote in message
...
"MH" wrote in message
...
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

EXCEL IF FUNCTION

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,29999999999999998889776975374843459576368331909 1796875), so Excel
correctly reports a difference.

The binary approximation to 0.7 has a decimal value of
0.699999999999999955591079014993738383054733276367 1875, so Excel correctly
calculates 1-0.7 to be
0.300000000000000044408920985006261616945266723632 8125. 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" wrote:

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

MH

"Sandy Mann" wrote in message
...
"MH" wrote in message
...
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

with @tiscali.co.uk

"MH" wrote in message
...
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

"Ah Ping" <Ah wrote in message
...
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

EXCEL IF FUNCTION

Thanks Jerry, that cleared that up.

MH

"Jerry W. Lewis" wrote in message
...
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,29999999999999998889776975374843459576368331909 1796875), so Excel
correctly reports a difference.

The binary approximation to 0.7 has a decimal value of
0.699999999999999955591079014993738383054733276367 1875, so Excel correctly
calculates 1-0.7 to be
0.300000000000000044408920985006261616945266723632 8125. 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" wrote:

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

MH

"Sandy Mann" wrote in message
...
"MH" wrote in message
...
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

with @tiscali.co.uk

"MH" wrote in message
...
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

"Ah Ping" <Ah wrote in message
...
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