At 16:54 29/05/2015 +0300, Algis Noname wrote:
I found a mistake. See file in the attachment.
There is no mistake - apart from in your understanding of what is happening.
Numbers are stored in computer in binary form, so what looks like a
precise number that terminates - such as your two numbers in A1 and
B1 - will not necessarily be stored exactly. When you subtract these
values in C1, the difference is stored as very slightly different
from the rounded value you see in the display. You can confirm this
by setting the cell formatting to show more fractional places. If I
do that, I see -196.809999999998 in C1. If you add exactly 196.81 to
that, you will not get exactly zero but the value you see
(correctly!) displayed in C3.
Numbers will be stored in your computer as 64-bit floating-point
numbers. The significant part of the numbers occupies 53 of those
bits, so they have precision to 53 binary digits, which corresponds
to about fifteen decimal digits. You will see that the difference
value above differs from the exact result at the fifteenth digit in
the number. You should take account of such rounding errors when
designing your spreadsheets.
There are workarounds:
o Format C3 to show a suitable number (perhaps 2) of fractional
places. You will see the displayed value rounded to zero.
o Go to Tools | Options... | OpenOffice Calc | Calculate and tick
"Precision as shown". Now change the cell formatting of C1 to have an
explicit value for "Decimal places" (say 2), rather than relying on
the General format you were using. Now you will see zero in C3 (and
in G1). But beware of genuinely incorrect values created by rounding
errors that you will see if you do this.
Incidentally, this matter has no connection with (Microsoft) Excel,
as your subject header suggests: you are handling a spreadsheet
document in the Calc component of Apache OpenOffice.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org
