Lazarus
Programming => Packages and Libraries => FPSpreadsheet => Topic started by: kjteng on July 16, 2019, 09:07:15 am

I noticed that fpspread is using the banker's rounding (round half to even) when the number format is set to '#,##0'.
i.e. 1.5 become 2, 2.5 is rounded to 2
Is there a simple way to show the number using common method of rounding (similar to excel/libreoffice) i.e. 1.5 > 2, 2.5>3 ?

Please try r7061. I replaced fpc's Round() function by a special one which avoids Banker's rounding. Please test and report back, there are probably cases left where still the old function is used.

Thank you master wp for the modification.
1.The display format seems ok to me now.
2. However, the round(x.5, n) function is still 'round to even' when n=1 (but round up when n equals 0, 2...). See attachment.

There's not much that I can do about this. It seems that the value 1.65 cannot be represented exactly, I see in the debugger the value 1.6499999999999999. This is smaller than 1.65 and, therefore, must round to 1.6 even without banker's rounding.
I can get the correctly rounded value when I use the datatype extended in the internal "RoundTo" procedure, but I am sure that there are cases again when this does not work. Because of the basic problem of floating point calculations, namely that floating point values theoretically are infinitely dense, but must be rounded internally due to the finite precision of 8 or 10 bytes.
I don't know what Excel and Calc are doing to get the "correct" value.

Noted. Many thank for your dedication and effort.

maybe it will be interesting to anyone, why is it in the debugger exactly 1.6499 (9): http://www.binaryconvert.com/result_double.html?decimal=049046054053 (http://www.binaryconvert.com/result_double.html?decimal=049046054053)
"human readable" number : (1)^S * 2^(E1023) * (1+M/2^52).
1.65 stored as: mantissa  1010011001100110011001100110011001100110011001100110 (2927339757790822), exponent  01111111111 (1023), sign  0, the final number: (1)^0 * 2^(10231023) * (1+2927339757790822/2^52) = 1 * 1 * (1+0,64999999999999991118215802998748) = 1,64999999999999991118215802998748