Restore mathematical logic for rounding down and up,
Currently gnumeric calculates results that contradict mathematical logic.
When rounding down, a value should not become larger, and when rounding up, it should not become smaller than the initial value.
In my opinion, the fuzziness caused by 'sub_epsilon' and 'add_epsilon' - with which actually the rounding! to clearly shortened strings should be adapted to user expectations? - also affects rounding down and rounding up.
While one can discuss whether the application for 'rounding' makes sense - I think a correction of the initial value considered deviated would be more correct - the effect on rounding down and up is clearly wrong.
Besides the contradiction to school mathematics, it unfortunately also blocks my work for more precise floating point calculations by making rounding 'at the limit' impossible.
According to the attached sheet it is not 'only a few' values that are affected, but in the range of representable precision partly more than half ( sheets 'rounddown_fails' - calculating, 'rounddown_with_snap-to' - copied results, 'roundup_fails' - calculating, 'roundup_with_snap-to' - copied results ).
finding_rounddown_up_fails.ods
The rate of affected values reduces with the 'precision distance' between initial value and rounded target, but also with 'shorter' targets deviations occur, e.g. [ edit 2022-07-18 reg. digits stolen by translator prog. ] '=rounddown( 0.2499999999999972, 2 ) '=rounddown( 0.249999999999999972, 2 ) [ /edit ] what should normally produce 0.24, actual result 0.25.
As long as there is no better solution for the rounding problem I suggest to mitigate the effects on rounddown and roundup with the following patches, they implement a 'John Denker correction', if a result is recognizably wrong -> correct it.
I think it is possible to code this more elegant, 'nicer' and faster, I just wanted to show the necessity and a possible solution, to make this 'suitable' I leave to people who can do this better than me ...
In the attached workbook also sheets 'rounddown_with_jdc' and 'roundup_with_jdc', both 'copied values' from a patched version.
( The sheets 'rounddown_fails_calc' and 'roundup_fails_calc' mark the points where LO Calc produces similar wrong results, they are not visible for the user reg. 'display prettyfying' and don't make it into the file with save as LO Calc writes up to 20 decimals digits in the file, but - same as display - only 15 of them are significant the rest is padded with zeroes. But wrong values are calculated, visible with 'rawsubtract', and go into downstream calculations ... )
proposed patches - deco-math_patch_P0005b_restore_math_logic_for_rounddown_roundup:
trunc:
static GnmValue *
gnumeric_trunc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
{
gnm_float number = value_get_as_float (argv[0]);
gnm_float digits = argv[1] ? value_get_as_float (argv[1]) : 0;
if (digits >= 0) {
if (digits <= GNM_MAX_EXP) {
gnm_float p10 = gnm_pow10 ((int)digits);
// edit b. - TO STAY - 2021-10-08: John Denker correction,
// in some cases, rounding to extrema, e.g. rounddown( nextafter( 0.25, -1 ); 16 ) for doubles or rounddown( nextafter( 0.5, -1 ); 19 )
// for 'long' instead of rounddown roundup occurs,
// partly reg. 'sub-/add_epsilon' fuzzyness, partly just FP-rounding-fail, John Denker proposed a corrective procedure,
// ori was: number = gnm_fake_trunc (number * p10) / p10;
gnm_float number_1 = gnm_fake_trunc( number * p10 ) / p10;
number = ( number > 0 ) ? number_1 - ( number_1 > number ) * gnm_pow10( -digits )
: number_1 + ( number_1 < number ) * gnm_pow10( -digits );
}
} else {
if (digits >= GNM_MIN_EXP) {
/* Keep p10 integer. */
gnm_float p10 = gnm_pow10 ((int)-digits);
// edit b. - TO STAY - 2021-10-08: John Denker correction,
// in some cases, rounding to extrema, e.g. rounddown( nextafter( 0.25, -1 ); 16 ) for doubles or rounddown( nextafter( 0.5, -1 ); 19 )
// for 'long' instead of rounddown roundup occurs,
// partly reg. 'sub-/add_epsilon' fuzzyness, partly just FP-rounding-fail, John Denker proposed a corrective procedure,
// ori was: number = gnm_fake_trunc (number / p10) * p10;
gnm_float number_1 = gnm_fake_trunc( number / p10 ) * p10;
number = ( number > 0 ) ? number_1 - ( number_1 > number ) * gnm_pow10( -digits )
: number_1 + ( number_1 < number ) * gnm_pow10( -digits );
} else
number = 0;
}
return value_new_float (number);
}
and for roundup:
static GnmValue *
gnumeric_roundup (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
{
gnm_float number = value_get_as_float (argv[0]);
gnm_float digits = argv[1] ? value_get_as_float (argv[1]) : 0;
if (digits >= 0) {
if (digits <= GNM_MAX_EXP) {
gnm_float p10 = gnm_pow10 ((int)digits);
// edit b. - TO STAY - 2021-10-08: John Denker correction,
// in some cases, rounding to extrema, e.g. roundup( nextafter( 0.25, 1 ); 16 ) for doubles or roundup( nextafter( 0.5, 1 ); 19 )
// for 'long' instead of roundup rounddown occurs,
// partly reg. 'sub-/add_epsilon' fuzzyness, partly just FP-rounding-fail, John Denker proposed a corrective procedure,
// ori was: number = gnm_fake_roundup (number * p10) / p10;
gnm_float number_1 = gnm_fake_roundup( number * p10 ) / p10;
number = ( number > 0 ) ? number_1 + ( number_1 < number ) * gnm_pow10( -digits )
: number_1 - ( number_1 > number ) * gnm_pow10( -digits );
}
} else {
if (digits >= GNM_MIN_EXP) {
/* Keep p10 integer. */
gnm_float p10 = gnm_pow10 ((int)-digits);
// edit b. - TO STAY - 2021-10-08: John Denker correction,
// in some cases, rounding to extrema, e.g. roundup( nextafter( 0.25, 1 ); 16 ) for doubles or roundup( nextafter( 0.5, 1 ); 19 )
// for 'long' instead of roundup rounddown occurs,
// partly reg. 'sub-/add_epsilon' fuzzyness, partly just FP-rounding-fail, John Denker proposed a corrective procedure,
// ori was: number = gnm_fake_roundup (number / p10) * p10;
gnm_float number_1 = gnm_fake_roundup (number / p10) * p10;
number = ( number > 0 ) ? number_1 + ( number_1 < number ) * gnm_pow10( -digits )
: number_1 - ( number_1 > number ) * gnm_pow10( -digits );
} else
number = 0;
}
return value_new_float (number);
}
Best Regards,
b.