Bug 61573 - when I read the result of ROUND formula by APACHE_POI, it returns unexpected value.
Summary: when I read the result of ROUND formula by APACHE_POI, it returns unexpected ...
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.14-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-09-29 04:49 UTC by hara katsu
Modified: 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.
Description 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
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.