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

[John Denker <jsd av8n com>]

On 7/7/21 9:15 AM, newbie nullzwei via gnumeric-list wrote:

>  > because Gnumeric is trying to emulate bug-for-bug compatibility with Excel?
>
> you made my day!, very nicely expressed.

No.

This has nothing to do with excel.
It has nothing to do with gnumeric.
It's not a bug in the code.

What makes you think you can take a number (on the order of 1)
modulo 10^-16 and get the right answer using floating point?

By way of background, let's do a simpler example. Write a C program
to calculate:
        ceil(2499999999999999.2)

The answer will come back
        2499999999999999

You may ask how can that be? How can ceil(x) be less than x?
The answer is that ceil(x) is never less than x, but in this
case x isn't what you think it is. The number 2499999999999999.2
cannot be accurately represented in floating point. The only
plausible possibilities are:
        2499999999999999.0
and
        2499999999999999.5

Obviously the former is better ... and when you take the ceil
of that, you get the answer I gave above.

The example featured in this thread is:
        =roundup(0,24999999999999997;16)

That's a slightly obfuscated version of the issue, but it's the same
issue.

Take-home lesson: In floating point, if you look at the 16th decimal
place, weird things are going to happen.

You could do the calculation in decimal, using string functions such
as mid(). You are free to do that, but don't expect gnumeric to
support it ... partly because it would yield only a small improvement,
in rare marginal cases.

A better way forward would be to avoid marginal cases. By that I mean
avoid numbers that are just barely (if at all) representable. Avoid
algorithms that are sensitive to the 16th decimal place.

========

One could perhaps argue that roundup(x) should be more defensive, so
that it never returns an answer less than x, but this would add
complexity for only very marginal gain. We agree that
        roundup(x) >= x
should be an invariant ... but there are plenty of other invariants
that are violated by floating point. For example:
        sin(x)^2 + cos(x)^2 - 1 == 0
should be an invariant, but it breaks for x=50 (among many many other
x values). It breaks by more than the machine epsilon.

Again: The way forward is to redesign your algorithms so that they are
not sensitive to the 16th decimal place. Do not require floating point
to do things it cannot possibly do.

Also again: This is not a gnumeric issue. One could (?) imagine making
gnumeric defensive about this-or-that marginal case, but that's an
unwinnable game of whack-a-mole combined with ESP. Do not expect
gnumeric to divine which marginal cases are important to you. Instead
design *your* algorithms to take care of *your* cases.