Aw: Re: Re: Re: deco-Math project, step 00_a: exact bin and dec 'ranges' (in gnumeric).

[newbie nullzwei <newbie-02 gmx de>]

hello @Steve, hello@all,

> because Gnumeric is trying to emulate bug-for-bug compatibility with Excel?

you made my day!, very nicely expressed.

yes, most likely such will be the reason.
 

regarding the 4 being rounded up ... the standard rounding process in gnumeric has no access to the digit '4', instead in the process of rounding to 16 digits the '4' in 17th place becomes powered up to a ',5' ('=0,3000000000000000044 * 1E16' -> 3000000000000000,5) and with rounding 'ties away from zero' as usual in spreadsheets this rounds up.

in rounding with IEEE proposal (standard) 'ties to even' this won't happen, thus one of the next steps i'm going to propose is to implement an additional rounding mode and to use that for 'correction rounding' of slightly deviating addition results. that might end the 0,30000000000000004 story.

0,300000000000000044~ as the result of 0,1+0,2 is a sort of 'zombie value', it needs 17 decimal digits for exact representation but step-1 increments in the 17th decimal place are not representable with doubles. regarding such one must decide to either eliminate them and replace by a value for which all 10 digits in last significant place are representable in binaries: the closest 16 digit value. IMHO Excel does similar by roundig to 15 digits, neglecting that in most ranges 16 digits are representable and 'countable'. or one must use the exact value for downstream calculations. IMHO gnumeric producs a mix, keeping the original value, but doing e.g. rounding as if it were replaced as Excel would do. 

> That is incorrect, it changes the value.

key '0,30000000000000004' in a cell, key '= {that cell} - 0,3' in another cell, change the display length of the first cell and watch the result in the second cell staying unchanged ... the value is not changed, what is displayed to you as 0,3 is 0,30000000000000004 in the background.

best regards,



b.

 

Gesendet: Mittwoch, 07. Juli 2021 um 12:03 Uhr
Von: "Steven D'Aprano" <steve pearwood info>
An: "newbie nullzwei via gnumeric-list" <gnumeric-list gnome org>
Betreff: Re: Re: Re: deco-Math project, step 00_a: exact bin and dec 'ranges' (in gnumeric).

On Mon, Jul 05, 2021 at 08:23:02PM +0200, newbie nullzwei via gnumeric-list wrote:

> > I don't understand what you mean by "crossover",
>
> =rounddown(0,24999999999999997;16) -> 0,250000000000000000000, and
> =roundup(0,24999999999999997;16) -> 0,249999999999999972244, the
> operation named 'down' going 'up', you commented about that about 2 weeks
> ago,

Are you talking about the threads in May?

https://mail.gnome.org/archives/gnumeric-list/2021-May/thread.html

Unless I have missed something, I agree that it looks like a bug in
Gnumeric's rounding.


> > but more importantly, I
> > don't understand your comment about 0.3. Gnumeric already rounds down:
>
> > 0,30000000000000004
>
> > to 0.3 (displayed to 16 decimal places). Of course the true value is:
> ...
>
> if it does like that it only adapt's the display, but doesn't change the
> value,

That is incorrect, it changes the value.


> and rounding the 17-sign-digit value 0,30000000000000004 to 16
> decimals with tools accessible for a user results in:
> '=round(0,30000000000000004; 16)' -> 0,300000000000000100.

I see. That does appear to be a bug.

Here are the four 64-bit floats close to 0.3, I'm going to name them A
through E to make it easier to talk about them. I'm going to shown them
as exact fractions and to full precision in decimal.

The floats immediately near 0.3 are:

A = 5404319552844593/18014398509481984
= 0.29999999999999987787546729123278055340051651000976562500
≈ 0.2999999999999999

B = 2702159776422297/9007199254740992
= 0.29999999999999993338661852249060757458209991455078125000
≈ 0.29999999999999993

C = 5404319552844595/18014398509481984
= 0.29999999999999998889776975374843459576368331909179687500
≈ 0.3

D = 1351079888211149/4503599627370496
= 0.30000000000000004440892098500626161694526672363281250000
≈ 0.30000000000000004

E = 5404319552844597/18014398509481984
= 0.30000000000000009992007221626408863812685012817382812500
≈ 0.3000000000000001

A and B are 1 Unit in Last Place (ULP) apart; so is B and C, and C and
D, and D and E. So as you go from A to E, the numbers are increasing.

Notice that there is no possible float *exactly* representing 0.3 in
decimal, that is because it would require an infinite number of bits in
binary:

0.01001100110011... (binary, base 2)

https://www.wolframalpha.com/input/?i=0.3+to+binary

So the closest float to 0.3 is C above.

So when we try to round D to 16 decimal places, the result ought to be
C, but it's not, instead it rounds up to E.

In Python, I get the expected value:

# Python 3.9
>>> round(0.30000000000000004, 16) == 0.3
True


but Gnumeric gives FALSE for the same calculation.

I agree with B that this is a bug in Gnumeric.

When we look at the 17th digit of D, it is a 4. That is less than 5, so
we should throw it and everything past it away, leaving 0.3000...0

But there is no exact float representing 0.3 exactly, the closest is D,
so the result should be D. Instead it gets rounded up to E 0.3000...1
which is wrong.

Between this and the previous thread back in May, I am satisfied that
Gnumeric's rounding is buggy. I don't know if this is because Gnumeric
is trying to emulate bug-for-bug compatibility with Excel.



--
Steve
_______________________________________________
gnumeric-list mailing list
gnumeric-list gnome org
https://mail.gnome.org/mailman/listinfo/gnumeric-list