Created attachment 38355 [details] Example I'am doing an simple arithmetic operation in cell , POI(Ver5.2.0) and Micro Excel calculatng results and the results are fine. The problem occurs when I update apache poi to version 5.2.2, the results don't match. e.g. Formula: C1=(A2+(B2-A2)*A1/10)-1 Where: A1=5 A2=(A3-1)*0.63+1 A3=1.35 B2=(B3-1)*0.5+1 B3=1.35 POI v5.2.0 results: C1=0.1977500000000001 -> Java logic -> Round(C1*100,2)=19.78 POI v5.2.2 results: C1=0.19774999999999987 -> Java logic -> Round(C1*100,2)=19.77 Micro Excel Calculation results: C1=0.19775 -> Excel Round -> D1=ROUND(C1*100,2)=19.78
Have you tried MROUND - it has more reliable rounding than thelegacy ROUND function
I added a test case using r1903122 and it seems to indicate that round and mround produce the correct results. Excel and POI use doubles for representing numbers and doubles are approximations. There was a small change in POI since POI 5.2.0 relating to how multiplication and division are done.
The problem is with the arithmetic operation. From this point, I have: POI v5.2.0 results: C1=0.1977500000000001 POI v5.2.2 results: C1=0.19774999999999987 Micro Excel Calculation results: C1=0.19775 I need to use POI 5.2.2 because you fixed a vulnerability issue but I obtain different results as you see. In my case i can not edit the spreadsheet, for this reason i can not use the MROUND function. I need to apply the rounding function in the code. Also, if I use xlwings python library I obtain the same results as version 5.2.0. Can you consider evaluate the multiplication and division operation? Thanks!
I'm not inclined to spend any time on this. It's impossible to keep everyone happy. I reiterate doubles are approximations. The difference from the desired result is miniscule. You can alter your own code to round the numbers the level of precision you need. You could also try https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/DataFormatter.html#formatCellValue-org.apache.poi.ss.usermodel.Cell-
Thanks for your comments, I will try your suggestions
It appears this change occurred in version 5.2.1 of the library: Version C1 Result A2 Result 5.1.0 0.1977500000000001 1.2205000000000001 5.2.0 0.1977500000000001 1.2205000000000001 5.2.1 0.19774999999999987 1.2205 5.2.2 0.19774999999999987 1.2205 > You can alter your own code to round the numbers the level of precision you need. If the results are rounded to 4 decimal places as suggested, the resulting answer is identical for A2, but different for C1: Version C1 Rounded Result A2 Rounded Result 5.1.0 0.1978 1.2205 5.2.0 0.1978 1.2205 5.2.1 0.1977 1.2205 5.2.2 0.1977 1.2205 Depending on how this value is used, this could be a significant difference.
You can round a double in Java and provide a rounding mode of your own choosing (see https://www.baeldung.com/java-round-decimal-number). You can use Excel ROUND or MROUND functions. Why are you trying to to round down when you can round to ROUND_HALF_UP?