Recent

Author Topic: fpspread - rounding for 0.5  (Read 488 times)

kjteng

  • Full Member
  • ***
  • Posts: 167
fpspread - rounding for 0.5
« 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  ?
« Last Edit: July 17, 2019, 02:33:49 am by kjteng »

wp

  • Hero Member
  • *****
  • Posts: 6235
Re: fpspread - rounding for 0.5
« Reply #1 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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 167
Re: fpspread - rounding for 0.5
« Reply #2 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.
« Last Edit: July 21, 2019, 07:37:28 am by kjteng »

wp

  • Hero Member
  • *****
  • Posts: 6235
Re: fpspread - rounding for 0.5
« Reply #3 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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 167
Re: fpspread - rounding for 0.5
« Reply #4 on: July 21, 2019, 03:45:09 pm »
Noted. Many thank for your dedication and effort.

iteh

  • New Member
  • *
  • Posts: 31
Re: fpspread - rounding for 0.5
« Reply #5 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

"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
« Last Edit: July 21, 2019, 05:16:51 pm by iteh »