[Bug 61573] New: when I read the result of ROUND formula by APACHE_POI, it returns unexpected value.

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 61573] New: when I read the result of ROUND formula by APACHE_POI, it returns unexpected value.

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61573

            Bug ID: 61573
           Summary: when I read the result of ROUND formula by APACHE_POI,
                    it returns unexpected value.
           Product: POI
           Version: 3.14-FINAL
          Hardware: PC
            Status: NEW
          Severity: normal
          Priority: P2
         Component: SS Common
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

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

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61573] when I read the result of ROUND formula by APACHE_POI, it returns unexpected value.

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61573

Nick Burch <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |NEEDINFO
                 OS|                            |All

--- Comment #1 from Nick Burch <[hidden email]> ---
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?

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61573] when I read the result of ROUND formula by APACHE_POI, it returns unexpected value.

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61573

--- Comment #2 from hara katsu <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61573] when I read the result of ROUND formula by APACHE_POI, it returns unexpected value.

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61573

hara katsu <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEEDINFO                    |NEW

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61573] when I read the result of ROUND formula by APACHE_POI, it returns unexpected value.

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61573

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |NEEDINFO

--- Comment #3 from Dominik Stadler <[hidden email]> ---
Can you attach the Excel file or provide a unit test which triggers this?

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61573] when I read the result of ROUND formula by APACHE_POI, it returns unexpected value.

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61573

--- Comment #4 from Javen O'Neal <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61573] when I read the result of ROUND formula by APACHE_POI, it returns unexpected value.

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61573

--- Comment #5 from Nick Burch <[hidden email]> ---
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

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61573] when I read the result of ROUND formula by APACHE_POI, it returns unexpected value.

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61573

hara katsu <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEEDINFO                    |NEW

--- Comment #6 from hara katsu <[hidden email]> ---
Thank you very much.
I will test it with the latest version.
Please tell me what the contents of the fixed bug are like.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61573] when I read the result of ROUND formula by APACHE_POI, it returns unexpected value.

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61573

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|---                         |WORKSFORME

--- Comment #7 from Dominik Stadler <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]