Bug 61573 - when I read the result of ROUND formula by APACHE_POI, it returns unexpected value.
when I read the result of ROUND formula by APACHE_POI, it returns unexpected ...
 Status: RESOLVED WORKSFORME None POI Unclassified SS Common (show other bugs) 3.14-FINAL PC All P2 normal (vote) --- POI Developers List

 Reported: 2017-09-29 04:49 UTC by hara katsu 2017-10-04 18:45 UTC (History) 0 users

Attachments

 Note You need to log in before you can comment on or make changes to this bug.
 hara katsu 2017-09-29 04:49:20 UTC ```Hi, would someone tell me the reason of following issue? Cell"CC24" contains the following formula; =ROUND（（I24*Q24*U24*Y24*AC24*(AG24+AK24*AO24/AS24)*(AW24-BA24)*(BE24-BI24)*(BM24+BQ24))*BU24/BY24 , -1 ） and the result is; before ROUND : 1514.6689635 after ROUND Excel result :1510 after ROUND POI result :1520 I expected that APACHE_POI returns "1510" as Excel, but the result is "1520". Additionaly, I know the following points; 1.APACHE_POI returns "1510" when I put additional parenthesis in formula. 　　ex： put parenthesis around "I24*Q24*U24*Y24*AC24" and "BU24/BY24" 　　　=ROUND(( ( I24*Q24*U24*Y24*AC24 ) *(AG24+AK24*AO24/AS24)*(AW24-BA24)*(BE24-BI24)*(BM24+BQ24))* ( BU24/BY24 ) ,-1) and the result is; before ROUND : 1514.6689635 after ROUND Excel result :1510 after ROUND POI result :1510 2.APACHE_POI returns "1510" when I substitute real numbers for functions. ex1. I24*Q24=100　⇒ substitute "100" for I24*Q24 (100*U24*Y24*AC24*(AG24+AK24*AO24/AS24)*(AW24-BA24)*(BE24-BI24)*(BM24+BQ24))*BU24/BY24 ex2. Q24*U24=0.21　⇒ substitute "0.21" for Q24*U24 (I24*0.21*Y24*AC24*(AG24+AK24*AO24/AS24)*(AW24-BA24)*(BE24-BI24)*(BM24+BQ24))*BU24/BY24 ex3. U24*Y24=0.03746862⇒ substitute "0.03746862" for U24*Y24 (I24*Q24*0.03746862*AC24*(AG24+AK24*AO24/AS24)*(AW24-BA24)*(BE24-BI24)*(BM24+BQ24))*BU24/BY24 ex4. Y24*AC24=1784.22⇒ substitute "1784.22" for Y24*AC24 (I24*Q24*U24*1784.22*(AG24+AK24*AO24/AS24)*(AW24-BA24)*(BE24-BI24)*(BM24+BQ24))*BU24/BY24 and the results are; before ROUND : 1514.6689635 after ROUND Excel result :1510 after ROUND POI result :1510``` Nick Burch 2017-09-29 04:57:06 UTC ```Does the same behaviour occur with Apache POI 3.17? If you aren't already using Java 8, does the same behaviour occur on Java 8 with POI 3.17?``` hara katsu 2017-09-29 05:17:31 UTC ```The version of Apache POI is 3.7. The Java version is 6. It is not possible to check the operation in Java 8 and 3.17.``` Dominik Stadler 2017-09-29 06:02:32 UTC `Can you attach the Excel file or provide a unit test which triggers this?` Javen O'Neal 2017-09-29 07:22:19 UTC ```POI 3.7 is no longer supported. I think github-43 fixed this issue in POI 3.17. If for no other reason, you should upgrade to 3.17 due to several important security fixes.``` Nick Burch 2017-09-29 08:38:06 UTC `Apache POI 3.7 is ancient. Please try upgrading to a more recent version, this bug may well have been fixed in the 7 years (7!) since 3.7 was released` hara katsu 2017-09-29 08:54:13 UTC ```Thank you very much. I will test it with the latest version. Please tell me what the contents of the fixed bug are like.``` Dominik Stadler 2017-10-04 18:45:18 UTC ```We don't have any single bug to state, rather Nick tried to state that there were literally hundreds of bugfixes since that version in all areas of Apache POI, so you should use a newer version for various reasons and we hope your problem will be solved as well then. I will close this for now, please reopen with an attached sample Excel file which allows to reproduce this if you still see this problem in version 3.17 or newer.```