On Wed, Jul 07, 2021 at 11:35:53AM -0700, John Denker via gnumeric-list wrote:
> This has nothing to do with excel.
> It has nothing to do with gnumeric.
> It's not a bug in the code.
It is a bug in Gnumeric's rounding. "Floating point is inexact" is not
an excuse to sweep incorrect rounding under the carpet. In this case,
rounding 0.30000000000000004 to 16 decimal places should result in the
float nearest to 0.3, regardless of whether ties round to nearest to to
even. If it doesn't, as in the case of Gnumeric, it is because your
rounding algorithm is poor.
> 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?
Python can do it. Python is written in C.
>>> round(0.30000000000000004, 16) == 0.3
True
I can do it, using pencil and paper.
I can think of at least two ways to implement correct rounding.
> 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
That example is not relevent to this rounding bug, because
2499999999999999.2 is not representable as a 64-bit float.
0.30000000000000004 *is* representable as a 64-bit float.
Please pay attention to what B and I are actually writing. Do us the
service of not assuming we are ignorant of what floats can represent
when we have already spent many emails demonstrating that we understand
the issue that not every decimal number is representable as a float.
> 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.
No, it is not the same issue. It is a distraction from the actual issue.
Please read my earlier post where I walk through the five C doubles
closest to the decimal number 0.3. Also look at B's analysis in his
reply to me.
I don't know whether he has correctly identified what algorithm Gnumeric
is using to perform rounding, but if he has, that algorithm is buggy and
should not be used.
> Take-home lesson: In floating point, if you look at the 16th decimal
> place, weird things are going to happen.
IEEE-754 floats are not magical, and there is nothing "weird" about the
16th decimal place.
Please stop handwaving away any bug in Gnumeric as "floats are weird".
Maybe you should spend some time reading Bruce Dawson's blog about
floating point. You might learn a few things from an expert. In
particular, I draw your attention to the first paragraph of:
https://randomascii.wordpress.com/2017/06/19/sometimes-floating-point-math-is-perfect/
"When programmers treat floating-point math as a dark art that can
return randomly wrong results then they do themselves (and the IEEE-754
committee) a disservice."
There is absolutely no reason why 0.30...04 which is 1 ULP from 0.3
should round *up* instead of down. That's just a bug in the rounding
algorithm used.
> 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.
They're not rare and marginal. Gnumeric's rounding is simply wrong, and
if you think that those wrong results are justified by "floats are
weird" and IEEE-754 rules, you are fooling yourself.
> 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.
An even better way forward is to stop justifying bugs in Gnumeric's code
by blaming the user.
> Again: The way forward is to redesign your algorithms so that they are
> not sensitive to the 16th decimal place.
If only Gnumeric's rounding functions followed your own advice, we would
not be having this argument.
> Do not require floating point
> to do things it cannot possibly do.
Python gets it right. Floating point is perfectly capable of doing
correct rounding.
--
Steve
_______________________________________________
gnumeric-list mailing list
gnumeric-list@gnome.org
https://mail.gnome.org/mailman/listinfo/gnumeric-list
