Aw: RE: follow up - newbie questions, sum over range, rounding of special problematic values

[newbie nullzwei <newbie-02 gmx de>]

 

hello @aguelzow, thanks for the quick reply,

that explains rounding 0.3000000000000000444 as the binary midpoint between 0.2999999999999999889 and 0.3000000000000000999 to the bigger value (tie to even, away from zero, bankers rounding or whatever),

but - sorry if i insist -,

~0.24999999999999997 has the bin representation:
                                          0 (1).1111111111111111111111111111111111111111111111111111 exp:01111111100 (dec:-3), 
the next bin value below! is: 0 (1).1111111111111111111111111111111111111111111111111110 exp:01111111100 (dec:-3), in dec: ~0.24999999999999994,
the next bin value above! is: 0 (1).0000000000000000000000000000000000000000000000000000 exp:01111111101 (dec:-2), in dec: 0.25000000000000000,

why does '=roundDOWN(0.24999999999999997,16)' go to the larger! value 0.25000000000000000, and '=roundUP(0.24999999999999997,16)' go to the smaller value ~0.24999999999999994? 

that's clueless to me ...

regards,

b.

 

Gesendet: Mittwoch, 19. Mai 2021 um 07:44 Uhr
Von: "aguelzow" <aguelzow pyrshep ca>
An: newbie-02 gmx de, gnumeric-list gnome org
Betreff: RE: follow up - newbie questions, sum over range, rounding of special problematic values

Your question numbered 1 should be directed to LibreOffice.

 

With respect to your question numbered 2, Morten pointed out that our calculations are on base 2, but you are still looking at rounding in base 10: if you had only 2 digits behind the point, you would have

0.00   i.e 0.00 in decimal

0.01   i.e 0.25 in decimal

0.10   i.e 0.50 in decimal

0.11   I.e 0.75 in decimal

1.00   i.e 1.00 in decimal

So where would 0.0111 round to?  It is closer to 0.50 than to 0.25.

 

Andreas

 

Andreas

 

 

 

Sent from my Galaxy

 

 

-------- Original message --------

From: "b. via gnumeric-list" <gnumeric-list gnome org>

Date: 2021-05-18 22:29 (GMT-07:00)

To: gnumeric-list gnome org

Subject: follow up - newbie questions, sum over range, rounding of special problematic values

 

 

hi @all, i'm new here, pls. excuse if i am not well adapted to the manners,

(asked something some time ago, got answer from a 'private email', but none to follow up questions, thus try to write to the list again, sorry if wrong,

>> 1. gnumeric produces much better sums over ranges than other spreadsheets which i have seen (LO calc and MS excel), can somebody explain by which means this is
>> achieved ('kahan summation' or 'summand ordering' or 'using FPU registers and long doubles' or other?) and / or give me a code pointer?

> 1. We use an extension of Kahan summation, see
https://gitlab.gnome.org/GNOME/goffice/-/blob/master/goffice/math/go-accumulator.c

thank you, i'm not a coder, looks nice and short and - as i have seen - works very good!,
is it ok to suggest the people at LibreOffice to have a look at this, there is one who wants to implement Kahan (with moderate success so far), and another is a little paranoid and thinks LibreOffice would get into trouble just by looking at your code / that of gnumeric at all, see https://bugs.documentfoundation.org/show_bug.cgi?id=137679#c42

>> 3. gnumeric produces quite accurate results (3.000000000000000444E-01 for '=0.1 + 0.2'), but has weaknesses when rounding that to e.g. 16 decimals digits, results in
>> 3.000000000000000999E-01 which is 'enhancing the fail'. ...

> 3. This is based on misunderstandings. Our calculations are based on
base 2.

there are other questionable rounding results, e.g. '=rounddown(0.249999999999999972244,16)' results in  0.250000000000000000000, while '=roundup(0.249999999999999972244,16)' produces 0.249999999999999888978 which - acc. my simple math knowledge - should be the other way around? (0.249999999999999972244 results from calculating 0.25 minus one ULP, i'd expect a 'roundDOWN' always to be less or equal to a 'roundUP', i suspect a systematic error, but might be wrong in my expectations as well, bear with me if the latter ... ),     

reg.



b.