***UNCHECKED*** [Bug 62738] New: RANDBETWEEN function rounds the value down to int

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

***UNCHECKED*** [Bug 62738] New: RANDBETWEEN function rounds the value down to int

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

            Bug ID: 62738
           Summary: RANDBETWEEN function rounds the value down to int
           Product: POI
           Version: 4.0.0-FINAL
          Hardware: PC
            Status: NEW
          Severity: major
          Priority: P2
         Component: SS Common
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

I have a cell in Excel file with the following formula:

=RANDBETWEEN(0, 9999999999)

When I call FormulaEvaluator::evaluateAll() the cell mostly gets the value
2147483647 (signed int32 max), which is not very random. The formula works fine
in Excel.

I briefly looked at the code of org.apache.poi.ss.formula.atp.RandBetween
class, and the int cast in the line

return new NumberEval((bottom + (int)(Math.random() * ((top - bottom) + 1))));

seems to be the problem.

--
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 62738] RANDBETWEEN function rounds the value down to int

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

PJ Fanning <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All

--- Comment #1 from PJ Fanning <[hidden email]> ---
I just changed the int cast to a long cast.
I'm sure there is a fair bit that could be done to improve the implementation
and testing of this function. Formula Evaluation is not an area that has
received much attention in recent times.

In the cases where users are reading in Excel files with POI, the Excel file
has both the formula and an already calculated value - most users are happy
with the pre-calculated value.
In the cases where users are writing Excel files with POI, they can add the
formula cells and let users open the files in Excel and have it calculate the
values.
There are some users who want POI to eval the formulas but we need more
contributors to help with building up this code and its tests.

--
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 62738] RANDBETWEEN function rounds the value down to int

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=62738

--- Comment #2 from Yegor Kozlov <[hidden email]> ---
I think we can return
java.util.concurrent.ThreadLocalRandom.current().nextDouble(bottom, top + 1).
It is the right way to generate a random number in a range in Java 1.7+.


Also, the code sets bottom to top if it is greater than it:

                if(bottom > top) {
                        top = bottom;
                }

This check changes the semantics of RANDBETWEEN. Excel returns #NUM!  in such a
case

--
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]