Bug 61573

Summary: when I read the result of ROUND formula by APACHE_POI, it returns unexpected value.
Product: POI Reporter: hara katsu <wtpgjadmwtpgjadw>
Component: SS CommonAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: 3.14-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description hara katsu 2017-09-29 04:49:20 UTC

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



    ex2. Q24*U24=0.21 ⇒ substitute "0.21" for Q24*U24



    ex3. U24*Y24=0.03746862⇒ substitute "0.03746862" for U24*Y24


     ex4. Y24*AC24=1784.22⇒ substitute "1784.22" for Y24*AC24



 and the results are; 
         before ROUND : 1514.6689635

        after ROUND Excel result :1510

        after ROUND POI result :1510
Comment 1 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?
Comment 2 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.
Comment 3 Dominik Stadler 2017-09-29 06:02:32 UTC
Can you attach the Excel file or provide a unit test which triggers this?
Comment 4 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.
Comment 5 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
Comment 6 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.
Comment 7 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.