tried it myself, it works to a good extent, see attached sheet and code below, but it is probably not 'well fitted' into the style and manners of gnumeric. if someone could rework this it would be very kind.
what is it needed for? just one example, if you want to calculate the 'ULP' of a value you could do it with '=2^(-52+int(log2(abs(X))))', and might fail because 'INT' and 'LOG' are sometimes a bit inaccurate near range limits. try with e.g. 0,249999999999999972.
with the help of 'bin_range' this works better, see sheet 'use_case' in attached file. that of course only works if you have compiled in the function 'bin_range' incl. referencing beforehand.
indeed only a very small improvement, but a very basic / elementary one.
additional an explanation / sample what i meant with '35 years old' and 'repeated again and again ...'
spreadsheets are doing some questionable things since long, e.g. following 'excel compatibility', and haven't solved the discrepancy between 'human expectations' - e.g. to round values up if you chopp ~5 - and technical recommendations - IEEE 754 recommends 'ties to even' as rounding mode - but sheets happily use 'away from zero' everywhere, despite 'ties to even' would work much better to round small FP artifacts back 'into the valley of zeros'.
it is! possible to improve plenty of such fails, only needs the will to do and some time and energy ...
best regards,
b.
code for 'bin_range' and 'dec_range':
/***************************************************************************/
// edit b. wip 2021-07-04: trying calculate the exact binary range of a value
// hard intersection at range borders, oversteering the deviations of log, int, ...
// edit b. todo: c/would be easier to extract the exponent from the float figure,
static GnmFuncHelp const help_bin_range[] = {
{ GNM_FUNC_HELP_NAME, F_("BIN_RANGE:binary range @{x}")},
{ GNM_FUNC_HELP_ARG, F_("x:number")},
{ GNM_FUNC_HELP_DESCRIPTION, F_("the exact binary range of a value @{x} is calculated.")},
{ GNM_FUNC_HELP_EXCEL, F_("This function is new and may differ from M$ Excel, LO Calc and prior Gnumeric behaviour.") },
{ GNM_FUNC_HELP_EXAMPLES, "=BIN_RANGE(2) --> 1"},
{ GNM_FUNC_HELP_EXAMPLES, "=BIN_RANGE(1.999999999999999) --> 0"},
{ GNM_FUNC_HELP_SEEALSO, "DEC_RANGE,INT,MOD"},
{ GNM_FUNC_HELP_END}
};
static GnmValue *
gnumeric_bin_range( GnmFuncEvalInfo *ei, GnmValue const * const *argv )
{
gnm_float number = value_get_as_float (argv[0]);
gnm_float bin_log = gnm_log2( number );
gnm_float logfrac = gnm_fmod( gnm_abs( bin_log ), 1 );
if( logfrac > 0 && bin_log < 0 )
logfrac = 1 - logfrac;
gnm_float logint = ( bin_log - logfrac );
gnm_float recalc = gnm_pow2( logint );
gnm_float bin_ULP_bin_log = gnm_pow2( -52 + (int)( gnm_log2( abs( bin_log ) ) ) );
return ( bin_log == logint && recalc > number )
? value_new_float( logint - 1 )
: value_new_float( logint );
}
/***************************************************************************/
// edit b. wip 2021-07-05: trying calculate the exact decimal range of a value
// hard intersection at range borders, oversteering the deviations of log, int, ...
static GnmFuncHelp const help_dec_range[] = {
{ GNM_FUNC_HELP_NAME, F_("DEC_RANGE:decimal range @{x}")},
{ GNM_FUNC_HELP_ARG, F_("x:number")},
{ GNM_FUNC_HELP_DESCRIPTION, F_("the exact decimal range of a value @{x} is calculated.")},
{ GNM_FUNC_HELP_EXCEL, F_("This function is new and may differ from M$ Excel, LO Calc and prior Gnumeric behaviour.") },
{ GNM_FUNC_HELP_EXAMPLES, "=DEC_RANGE(101) --> 2"},
{ GNM_FUNC_HELP_EXAMPLES, "=DEC_RANGE(99.99999999999999) --> 1"},
{ GNM_FUNC_HELP_SEEALSO, "BIN_RANGE,INT,MOD"},
{ GNM_FUNC_HELP_END}
};
static GnmValue *
gnumeric_dec_range( GnmFuncEvalInfo *ei, GnmValue const * const *argv )
{
gnm_float number = value_get_as_float (argv[0]);
gnm_float dec_log = gnm_log10( number );
gnm_float logfrac = gnm_fmod( gnm_abs( dec_log ), 1 );
if( logfrac > 0 && dec_log < 0 )
logfrac = 1 - logfrac;
gnm_float logint = ( dec_log - logfrac );
gnm_float recalc = gnm_pow10( logint );
gnm_float dec_ULP_dec_log = gnm_pow10( -15 + (int)( gnm_log10( abs( dec_log ) ) ) );
return ( dec_log == logint && recalc > number )
? value_new_float( logint - 1 )
: value_new_float( logint );
}
/***************************************************************************/
---
> You should read these:
be assured: i did ... not all of them to the full extent, but most of them multiple times ... i do! have a understanding of fp-math,
