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.
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.
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
the int cast was removed - will close this - if we want to revisit, we can add a new issue