About Us

MH[_2_]

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
challenge! javascript function into excel function	Kamila	Excel Worksheet Functions	2	February 19th 07 06:35 AM
FUNCTION GETPIVOTDATA MICROSOFT EXCEL 2003 VS EXCEL 2004 FOR MAC	FRANCISCO PEREZ-LANDAETA	Excel Worksheet Functions	0	July 6th 06 01:25 PM
FUNCTION GETPIVOTDATA EXCEL 2003 v EXCEL 2004 FOR MAC	FRANCISCO PEREZ-LANDAETA	Excel Worksheet Functions	0	July 6th 06 12:56 PM
Excel Workday Function with another function	Monique	Excel Discussion (Misc queries)	2	April 27th 06 01:11 PM
Can you nest a MID function within a IF function in Excel	Dawn-Anne	Excel Worksheet Functions	2	March 4th 05 01:37 PM

Menu

About Us