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

[Steven D'Aprano <steve pearwood info>]

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