P
p4t
I am a newbie on Excel, and I need help.
=TRUNC((59,4-59)/0,2)
returns 1 !!!!
Can anyone explain me why this happens????
Please help, I do not have much time.
Thanks in advance!
I am a newbie on Excel, and I need help.
=TRUNC((59,4-59)/0,2)
returns 1 !!!!
Can anyone explain me why this happens????
Please help, I do not have much time.
Thanks in advance!
N
Nick Hodge
Any floating point calculations made by any computer is only going to be an
'approximation'. The fact that you are making many calculations on small
floating point exaggerates it. You may want to check out the ROUND, CEILING
or FLOOR functions to overcome this or at least get reliable results
For a better explanation of computer science and floating point maths, look
here
http://www.cpearson.com/excel/rounding.htm
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
'approximation'. The fact that you are making many calculations on small
floating point exaggerates it. You may want to check out the ROUND, CEILING
or FLOOR functions to overcome this or at least get reliable results
For a better explanation of computer science and floating point maths, look
here
http://www.cpearson.com/excel/rounding.htm
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
J
Jerry W. Lewis
Your inputs must be approximated, so the output is necessarily only
approximate.
Assuming that your regional settings define a comma as the decimal
separator (otherwise the formula contains an error), the argument to
TRUNC is 1,99999999999999. Because 59,4 has no exact binary
representation, it must be approximated, the approximation dictated by
the IEEE standard (used by almost all software, not just Excel) is
59,39999999999999857891452847979962825775146484375
when you subtract 59 from it, Excel correctly displays the result as
0,399999999999999
and everything else follows.
If your calculation is not robust (as with TRUNC) to approximations
beyond the 15th figure, then either work with integers as long as possible
=TRUNC(594-590/2)
or round after subtraction
=TRUNC(ROUND(59,4-59;1)/0,2)
Jerry
approximate.
Assuming that your regional settings define a comma as the decimal
separator (otherwise the formula contains an error), the argument to
TRUNC is 1,99999999999999. Because 59,4 has no exact binary
representation, it must be approximated, the approximation dictated by
the IEEE standard (used by almost all software, not just Excel) is
59,39999999999999857891452847979962825775146484375
when you subtract 59 from it, Excel correctly displays the result as
0,399999999999999
and everything else follows.
If your calculation is not robust (as with TRUNC) to approximations
beyond the 15th figure, then either work with integers as long as possible
=TRUNC(594-590/2)
or round after subtraction
=TRUNC(ROUND(59,4-59;1)/0,2)
Jerry
P
p4t
I really thank you for your replies.
I figured this out, from microsoft's article
http://support.microsoft.com/default.aspx?scid=kb;en-us;78113
Maybe it's time to redesign the whole IEEE specifications,
because Excel cannot fail where a handheld calculator succeeds!!!
I used ROUND function to overcome the problem.
Thank you very much!
I figured this out, from microsoft's article
http://support.microsoft.com/default.aspx?scid=kb;en-us;78113
Maybe it's time to redesign the whole IEEE specifications,
because Excel cannot fail where a handheld calculator succeeds!!!
I used ROUND function to overcome the problem.
Thank you very much!
J
Jerry W. Lewis
I don't think you adequately understand the issue. Just as there is no
exact decimal representation for 1/3, there is no exact binary
representation for 59,4 or 0,2 or most other decimal fractions. The
only way to avoid approximating these numbers is to do decimal math
(which still would have to approximate numbers like 1/3, etc.) or do
symbolic math. Either option would increase memory requirements and
slow calculations. Where you appear to avoid these problems, one of the
following is true:
- the software is not doing binary math (very unusual in computer
software)
- the software is using guard digits, which has two down-sides (1) you
do not have access to all of the precision that would otherwise be
available (2) it only puts a veneer on the issue instead of eliminating
it, thus promoting sloppy design of calculations that will still reveal
the issue in some calculations.
- the software is basing results on assumptions that will decrease the
accuracy of results under some circumstances.
Jerry
exact decimal representation for 1/3, there is no exact binary
representation for 59,4 or 0,2 or most other decimal fractions. The
only way to avoid approximating these numbers is to do decimal math
(which still would have to approximate numbers like 1/3, etc.) or do
symbolic math. Either option would increase memory requirements and
slow calculations. Where you appear to avoid these problems, one of the
following is true:
- the software is not doing binary math (very unusual in computer
software)
- the software is using guard digits, which has two down-sides (1) you
do not have access to all of the precision that would otherwise be
available (2) it only puts a veneer on the issue instead of eliminating
it, thus promoting sloppy design of calculations that will still reveal
the issue in some calculations.
- the software is basing results on assumptions that will decrease the
accuracy of results under some circumstances.
Jerry