hi,
>You are playing at the edge of the maximum mantissa length. Did you ever
try using that number in LibreOffice or Excel?
i know ;-)
i do also know that Excel and LO try to avoid user irritations by some 'rounding' or 'bit truncating', know that it doesn't work well and so on, but that doesn't forbid to strive for improvements / corrections in gnumeric ...
äh ... that's why a mantissa has 52(53) bits? to calculate with 52(53) bits? i don't think the last bits are anyhow 'weak' or so?
it is not! that i need such exact calculations, it is that i think spreadsheets need them to avoid trapping in '=' vs. '!=' problems, failed '<', '=', '>' comparisions and similar.
and to stop questions about 0,1 + 0,2 != 0,3, a correct working of round(0.1 + 0.2, 16) would be nice, doesn't work acc. Morten, but a rounddown could work - IMHO,
gnumeric claimed 'accuracy' and staying away from calc and excel's helpless 'prettyfying', and fulfills that quite well, just on the outer corner of possibilities i found some deviations ... as they are in the range of representable IEEE doubles - 0.24999999999999997 is 0 (1).1111111111111111111111111111111111111111111111111111 exp 01111111100 (+ 1.9999999999999998 * 2^-3) - a correct result should be possible, and as the correct results are! there, just sometimes crossover! between roundup and rounddown, i'm quite sure there is a little unwanted bug in the code.
it's less the input value triggering difficulties, more the amount of decimals requested. for most values 16 digits are safe (e.g. in the ranges [1 .. 8[ and [10 .. 64[ ). while in others (e.g. [0.5 .. 1[, [8 .. 10[ and [64 .. 100[ ) only 15 digits are qualified. might be that the error evolves from that, a rounding to 15 digits somewhere?
if one wants to decide between values where 16 digits are qualified and others where it's only 15 and which need special handling respecting that: the only possibility i found so far is to compare the binary and decimal ULPs, if '=2^(-52 + int(log(abs(x);2)))' is less than '=10^(-15 + int(log(abs(x);10)))' 16 digits of 'x' are 'good', if not the value is in one of the underdetermined ranges and needs treatment as 15 decimal digits (not 'decimals', but digits in total).
i also tried 'manual' rounddown of 0.24999999999999997 in 'weitz' (
www.weitz.de/ieee), multiply it by 1E16, get 2499999999999999.5, truncate the fractional part, get 2499999999999999, divide that by 1E16, get 0.2499999999999999 - done, correct, possible.
and roundup: multiply with 1E16, get 2499999999999999.5, add one -> 2500000000000000.5, truncate the fraction -> 2500000000000000, divide by 1E16 -> 0.2500000000000000, works,
tried manual rounddown in a sheet: '=(int(0.249999999999999972244*10^16))/10^16' failed, the failing step is 'int' of 2499999999999999.50 after the multiplication, results in 2500000000000000.00, neither trunc nor floor helped me out, but mod '=(x*10^16-mod(x*10^16,1))/10^16' works better,
tried manual roundup in a sheet: '=int(0.249999999999999972244*10^16+1)/10^16' failed, gave an overshot to 0.250000000000000111022, didn't try trunc, floor, ceil or similar, just mod, '=(0.249999999999999972244*10^16+1-mod(0.249999999999999972244*10^16+1,1))/10^16' helped, ->0.250000000000000000000,
to avoid an overshot for values at a border subtracting one ULP seems neccessay, '=(A3*10^B6+1-2^(-52+int(log(abs(A3*10^B6),2)))-mod(A3*10^B6+1-2^(-52+int(log(abs(A3*10^B6),2))),1))/10^B6' looks a little 'complex', but i'm sure an experienced coder can do better ...
if i - and gnumeric! - can calculate the correct results in a sheet i'm sure it can be done in code, and think it should be done, the 'int issue' might affect other calculations as well ...
a correcting rounding of 0.1 + 0.2 to 16 decimals can be reached with '=(A3*10^16+0.5-2^(-52+int(log(abs(A3*10^16),2)))-mod(A3*10^16+0.5-2^(-52+int(log(abs(A3*10^16),2))),1))/10^16' for '=0.1 + 0.2' in A3,
not yet sure if that would work as a general solution, but in good hope a solution can be found ...
best regards,
b.
P.S. @User Hayden: 'It was called Binary Coded Decimal. ...', i also know about such, but today we have a wide installed base of 'IEEE machines', and i'm not searching for 'specialised computation', but for gnumeric to avoid fails and keep it's position as the spreadsheet with the best mathematical precision / correctness, the more of that works the less unwanted questions you'll see here ...
---
Gesendet: Sonntag, 23. Mai 2021 um 20:42 Uhr
Von: "Andreas J. Guelzow" <aguelzow pyrshep ca>
An: gnumeric-list gnome org
Betreff: Re: follow up II - newbie questions, sum over range, rounding of special problematic values
Hi,
On 2021-05-22 7:26 p.m., Steven D'Aprano wrote:
>
> input 0.24999999999999997
You are playing at the edge of the maximum mantissa length. Did you ever
try using that number in LibreOffice or Excel?
Andreas
_______________________________________________
gnumeric-list mailing list
gnumeric-list gnome org
https://mail.gnome.org/mailman/listinfo/gnumeric-list
