A1: 8192,3
B1: 7774,3
C1: =A1-B1
cell format: Standard;-Standard;;
C1:
417,999999999999
?
Hello @ANG,
Not with me (see ANG.ods (7.9 KB)).
In your document, A1 and B1 contains formulas, or just numbers? Please, edit your question to add more info. Thanks.
Edit:
Edit 2:
see
bug absent if:
cell format: Standard
also, in FAQ:
“Calculations with those numbers necessarily results in rounding errors, and those accumulate with every calculation”
where here “roundind”? (subtraction)
ps: in MS Excel bug is absent.
If you mean that with “default” cell width, and with plain Standard cell format, you see expected 418, but when you change the cell format to Standard;-Standard;;, it becomes ### in the cell, and when you widen it, you see that imprecise result:
The plain Standard format (unlike any other format, including user-defined formats that use Standard in its parts) has a special property of automatically adjusting to the cell width (i.e., its decimal count adjusts itself to fit into the cell). For wider cells, it shows more decimals; for narrower cells, it rounds to the given width.
So test this: put the same =A1-A2 formula into two cells A3 and A4; have A3 formatted as Standard, and A4 formatted as Standard;-Standard;;; see that A3 shows 418, while A4 shows ### indicating “too narrow cell for the given number”; widen the column to make A4 show its value, and see that A3 (formatted as Standard!) also shows now that “unexpected” result.
Also see that your “in MS Excel bug is absent” only shows that MS Excel, by default, shows fewer decimals, and - as shown by @EarnestAl - shows exactly the same result (which is the result of computer floating-point operations described in the FAQ) when showing enough decimals (12+).
The rounding happens at every operation that converts the decimal number (say, 8192,3, 7774,3) into the binary floating-point number format used internally, and also at every operation where another number appears that can’t fit into this representation. You should not imagine that when you enter 8192,3, Calc keeps 8192,3 inside. No, it keeps 8192,29999999999927240423858165740966796875 (represented as 0 10000001100 0000000000000010011001100110011001100110011001100110 in the IEEE 754 binary64 format), which is not the same number. This number is the closest to what you entered, that can be stored in the machine representation. So rounding (to binary, not decimal rounding!) happened already in A1 (and A2).
1 Like
- cell format is the same in both cases(row 3)
thanks… sad, it turns out the calculations are extremely inaccurate in principle in any case. at least in libre.
… which is just what I wrote:
Add enough decimals to the format, and it will show the 9s.
By the way: seeing your localization, this could be interesting to you: [Решено] LO Calc. Вычисления с дробями. Простой пример, странное поведение. : Открытые офисные пакеты. Форум поддержки пользователей
1 Like
but after all, we are talking about a certain format of cells(Standart;-Standart;; - for libre and General;-General;; - fo excel), and not “by default”. and excel shows right result. but libre - not.
many thanks. this interesting.
If your concern is just formatting (and having that imprecise result under the hood is understood), you might want to ask to change the default number of decimals for the Standard as part of user format - that would be an enhancement request.
1 Like
Not especially in libre, but in every spreadsheet and database. If you need mor precision check software for math like maple and there are several libraries around to handle arbitrary length calculations. You will never reach perfection with Pi …
.
From times long ago, I remember also BCD-coding, but I don’t think this is an option now.
Youre telling [redacted], it may be inaccurate in principle but on a non-significant and irrelevant Level.
[edit]: so lets rephrase:
your statement: »calculations are extremlely inaccurate« … is extremely nonsense !!!
:[edit end]
( just to remind: the Error happens after ~14 significant digits…
with 14 digits of PI you calculate the circumference of a circle with 1000 km diameter about 0.5 micrometer too small )
3 Likes