[Solved] Unexpected value after repeated increments

[clag]

Hello and good day to all
I tried to generate columns of numbers with constant downward increments of 0.1, but after 20-50 steps, AOO starts to stutter and generates incorrect values. It doesn't matter whether I use the automatic mouse drag version or a formula with -0.1.
Is this only happening on my system, or do others have the same problem?
I first noticed the issue when the expected zero in the format 0.00 wasn't zero, than I looked closer what happens

calculation_error.ods

(22.82 KiB) Downloaded 66 times

Example table is attached.

greets clag

[robleyd]

Calc: Accuracy problem

Round the result to e.g. 2 places.

[clag]

Hello Robleyd
yeah I have done it like you suggested
I just wonder why few 20+1 create such e problem there was just integers

anyway thanks clag

[Lupp]

See attached example which contains some explanations:

aoo_113299_DyadicRoundingVsDecimalExpectation.ods

(84.27 KiB) Downloaded 77 times

[clag]

Hello Lupp

Thanks for your explanation, the solution for my problem is than simple: I just create a column with increase the number by 1 and then divide the result by 10, copy and past the Numbers and everything works perfectly.
I really appreciate it, if there is a logic it is easy to remember

greets clag

[Lupp]

(1) ... I just create a column with increase the number by 1 and then divide the result by 10, ...
(2) ... copy and paste the numbers and everything works perfectly.

(1) Good idea.

(2) Why? Do the division in an extra column. You may hide the helper column with integers then.
If you now need to change the starting value, no Copy/Paste will be needed.

[clag]

Moin Lupp and everybody else

the reason for copy and pastet only the values is, that I think Calc calculate always the whole sheet again or all sheets if there are relations between them, if there is a change in any data on that sheet. I just want to reduce the calculating work for Calc.
I just generate this colums only one time and it will not change, so I think its unnecsessery work for Calc to recalculate this values ervery times again, when the formulas are still in the cells. !?

thanks clag

[Lupp]

There are a few volatile functions which cause the recalculation of formulas they are used in "on next to any event" (including e.g. Del applied to an already blank cell, but not a change of the selection). The relevant examples are NOW(), DATE(), RAND(), RANDBETWEEN().
Recent versions of LibreOffice have also the non-volatile versions RAND.NV() and RANDBETWEEN.NV(). There are also tools to insert fix values of date or time without using a cell formula.

However, at the heart of modern spreadsheet software are algorithms which restrict the need of recalculations as far as possible. The principle may be called "Recalculate On Demand".
As far as I understand it, I would describe it this way:
Cells not in the current view referencing cells or ranges which changed contents or results are not necessarily recalculated immediately, but marked "dirty". If anywhere a cell which contains a reference to a dirty cell shall be recalculated (moved into the view e.g.), there is first a check if dirty cells are referenced. If so, these are recalculated in advance of recalculating the formula having caused the check.

Things should be better explained here: https://lord.io/spreadsheets/ .

[Cells using the mentioned .NV functions of LibO and nothing else except constants/literals are never marked "dirty".]

[morchat]

The spreadsheet stores all numerical values ​​as floating-point numbers. This means that some values ​​cannot be finitely transformed, hence the results. These differences, of course, appear at the far decimal places. If these numbers are only for presentation purposes, they simply need to be formatted appropriately. However, if they are involved in calculations, they should be rounded using the ROUND() function, then copied (CTRL+V) and pasted back as values ​​using Paste Special (SHIFT+CTRL+V).