hi @all,

one more question for today … IMHO difficult …

short ver.: does anybody know how to correctly round ‘=0,1 + 0,2’ to 16 decimals? 0,300000000000000099920 is not a good result.

‘=0,1 + 0,2’ gives a wrong result with fp-math, namely ~0,300000000000000044409 (you can have a look with ‘www.weitz.de/ieee’, and there is even an own website why this is so: https://0.30000000000000004.com/).

Calc calculates this too, but does not show the deviation (display rounded to 15 digits). Calc continues to calculate with the wrong value, and with ‘=RAWSUBTRACT(0,1 + 0,2; 0,3)’ → ~5,55111512312578E-17 you can display it.

(up to here Excel is just as dumb, it doesn’t know ‘rawsubtract’, you have to put the formula in parentheses instead).

Normally it should be quite easy to repair 0,30000000000000004 by rounding to 16 decimal places. Unfortunately, this only works in Excel, which ‘fixes’ the value even if you round to 25 decimal places (the error should actually be preserved).

Calc is ‘worse’ and makes the error bigger when rounding! ‘=ROUND(0,1 + 0,2; 16)’ gives something with ~0,300000000000000099920, of which Calc does not show the last disturbing digits, but ‘=RAWSUBTRACT(ROUND(0,1 + 0,2; 16); 0,3)’ → 1,11022302462516E-16.

(I guess calc compares for rounding with the ‘correct’ double value for 0,3 and that is a bit smaller than real 0,3 namely ~0,299999999999999988898, from that the rawsubtract display comes up with 5,5 instead of 4,4; and as the difference is over something with 5 calc thinks to round up.)

Actually … one could simply ignore such small errors, but as:

- they are not necessary,
- they can add up in more complex calculations, multiply or even exponentiate and then disturb,
- they also disturb comparisons like ‘=’, ‘<’ or ‘>’ sensitively,
- many ‘higher functions’ are based on the correct functioning of such simple functions, …

it would be nice if this would work better … and it is possible, see Excel.

Does anyone have an idea?

(please do not! comment ‘round to less amount of decimals’, 1. that could fail too (for other values) 2. i’d like to preserve the full value.)