excel cannot count: 4513,77-4500.It shows 13,77 but <> 13,77.Try!

M

mik

lets try to compare the result
write to cell:
=4513,77-4500=13,77
its true or false?
(excel 2003 Sp1 professional)
do i need a coproccessor? :)


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...9-3341e6cb1d34&dg=microsoft.public.excel.misc
 
S

SteveW

I'll stick with your , for decimal format

Never rely on Excel always giving 0,000000000000000 to an answer
here if you put those values in cells
a1=4513,77
a2=4500
a3=a1-a2 gives 13,77

I added a4=a3-13,77
and got 4,36984E13 - not quite Zero

you should use ABS and a mininmum accepted tolerance on your answer

=abs(4513,77-4500-13,77)<0,0000001

Steve
 
R

Ron Rosenfeld

lets try to compare the result
write to cell:
=4513,77-4500=13,77
its true or false?
(excel 2003 Sp1 professional)
do i need a coproccessor? :)
Click to expand...

This has been discussed on this NG ad infinitum. Do a search of the MSKB for
Rounding Errors in Excel. It is a consequence of the design of this (and most
other) spreadsheet programs which adhere to the IEEE specifications and the
inability of representing some decimal numbers as exact binary equivalents.

If you are looking for precision to a certain number, you must round to that
precision.

=ROUND(4513.77-4500,3)=13.77


--ron
 
J

Jerry W. Lewis

Try =4513,77-4500 and format to 13 decimal places. You will get
13,7700000000004
which is why Excel returns False for =4513,77-4500=13,77

In problems like this, where you know where the last figure should be,
rounding can avoid surprises. Excel will return True for
=ROUND(4513,77-4500;2)=13,77

As others have noted, this is due to Excel (and almost all other computer
software) doing binary math. In binary, there are only 4 2-decimal place
fractions that can be exactly represented ( ,00 ,25 ,50 ,75 ) all others must
be approximated (just as 1/3 can only be approximated as a decimal fraction).
What you are seeing is the the unavoidable consequence of the initial binary
approximation to 4513,77. That approximation has a decimal value of
4513.77000000000043655745685100555419921875
which you don't directly see because Excel displays no more that 15 digits
of a number (as documented in Help).

Jerry
 
Top