ROUND problem excell 2003 70 – 69,3 results in 0,70000000000000300

J

Joost Keijer

We have the following problem with excel 2003

When subtracting something from 70 we get round of problems
Example: 70 – 69,3 results in 0,700000000000003000000000000000

Is this a known issue?

Regards,

Joost Keijer
 
N

Niek Otten

Hi Joost,

<Is this a known issue?>

Yes. Look here:

http://support.microsoft.com/kb/78113

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|
| We have the following problem with excel 2003
|
| When subtracting something from 70 we get round of problems
| Example: 70 - 69,3 results in 0,700000000000003000000000000000
|
| Is this a known issue?
|
| Regards,
|
| Joost Keijer
|
 
G

Gary''s Student

This is just normal rounding error. Occurs in all versions of Excel. Use:

=ROUND(70-69.3,1)

if greater precision is needed
 
H

Hans

I have the same problem while using Excel2007.

ROUND(1.2 - (70-69.3)) gives 0 and should be 1.....
 
R

Roger Govier

Hi Hans

ROUND(1.2 - (70-69.3),0) correctly gives 0
ROUND(1.2 - (70-69.3),1) gives 0.5
ROUNDUP(1.2 - (70-69.3),0) gives 1
 
H

Hans

Hi Roger,

Thank you for efforts in helping solving this strange "phenomenon".

I understood the function ROUND works like this for numbers with one decimal:
0.4=>0, 0.5=>1, 0.6=>1, 1.5=> 2, 2.5=>3 etc.

So ROUND (0.5;0) should give 1
70-69.3 should give 0.7 and not 0.7000000000000003
And 1.2 - (70 - 69.3) should give 0.5 exactly and not 0.499999999999997
So ROUND ((1.2 - (70-69.3));0) should give 1?
 
B

Bernard Liengme

You need to learn how computer store numbers. See for example:
INFO: Visual Basic and Arithmetic Precision
http://support.microsoft.com/defaul...port/kb/articles/Q279/7/55.ASP&NoWebContent=1
(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/defaul...pport/kb/articles/Q42/9/80.ASP&NoWebContent=1
http://support.microsoft.com/kb/78113/en-us
What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm
best wishes
 
H

Hans

I am just a simple Excel user. Do I need to learn how the computer stores
numbers?
Maybe 30 years or more ago.

I expected Excel (ie. the ROUND-function) to do a proper job in handling
simple decimal numbers (1 decimal accuracy). I will try to find a work around
in this case, but I have lost absolute trust in future calculations of
Excel......
 
B

Bernard Liengme

Only if you want to understand why you get small non-zero numbers when you
expect exactly zero. ROUND always works.
best wishes
 
J

Jerry W. Lewis

A simple way to think about it is to use the documented limit of 15 figures.
The problem then becomes
70,0000000000000??
-69,3000000000000??
-------------------------
0,7000000000000??
which is consistent with Excel's reported result of
0,700000000000003

Understanding Bernard's references are not required to use Excel, but they
will make it clear what is actually going on. The math is exactly correct,
but Excel is working with numbers that are slightly different than you
expect. Also note that this is a finite precision issue (common to all
software except symbolic manipulators) compounded by decimal/binary
conversions (common to almost all software), and not just an Excel issue.

Most terminating decimal fractions (including ,3 and ,7) have no exact
binary representation, and hence must be approximated. When you do math with
approximate inputs, that the output is only approximate should be no
surprise. The exact value of the approximation to 69.3 is
69,2999999999999971578290569595992565155029296875
which when subtracted from 70, correctly yields
0,7000000000000028421709430404007434844970703125

Excel is working with and returning these exact values, but (as documented)
will never display more than 15 digits of any value. If you ask for more
than 15 digits, you will get meaningless trailing zeros. You can see more
using my VBA conversion functions

http://groups.google.com/group/micr..._frm/thread/9f83ca3dea38e501/6efb95785d1eaff5

Jerry
 
Top