Lazarus

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

Title: fpspread - rounding for 0.5
Post 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  ?

Title: Re: fpspread - rounding for 0.5
Post by: wp on July 20, 2019, 06:54:19 pm

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.

Title: Re: fpspread - rounding for 0.5
Post by: kjteng on July 21, 2019, 07:32:35 am

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.

Title: Re: fpspread - rounding for 0.5
Post by: wp on July 21, 2019, 11:17:32 am

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.

Title: Re: fpspread - rounding for 0.5
Post by: kjteng on July 21, 2019, 03:45:09 pm

Noted. Many thank for your dedication and effort.

Title: Re: fpspread - rounding for 0.5
Post by: iteh on July 21, 2019, 04:43:30 pm

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^(E-1023) * (1+M/2^52).

1.65 stored as: mantissa - 1010011001100110011001100110011001100110011001100110  (2927339757790822), exponent - 01111111111 (1023), sign - 0, the final number: (-1)^0 * 2^(1023-1023) * (1+2927339757790822/2^52) = 1 * 1 * (1+0,64999999999999991118215802998748) = 1,64999999999999991118215802998748