Bug 56741 - [PATCH] Floating point rounding problems with ROUNDDOWN function
Summary: [PATCH] Floating point rounding problems with ROUNDDOWN function
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.11-dev
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Keywords: PatchAvailable
Depends on:
Reported: 2014-07-18 14:32 UTC by Cédric Walter
Modified: 2019-03-10 16:40 UTC (History)
0 users

Patch.txt (3.22 KB, patch)
2014-07-18 14:57 UTC, Cédric Walter
Details | Diff
test-data/spreadsheet/FormulaEvalTestData.xls (173.00 KB, application/vnd.ms-excel)
2014-07-18 14:58 UTC, Cédric Walter

Note You need to log in before you can comment on or make changes to this bug.
Description Cédric Walter 2014-07-18 14:32:40 UTC
Similar to the patch provided in Bug 56315
the ROUNDDOWN() function is not behaving like excel and required a similar patch as for the ROUND() function.

New code

public static double roundDown(double n, int p) {
 double retval;
 if (Double.isNaN(n) || Double.isInfinite(n)) {
   retval = Double.NaN;
 } else {
   retval = new BigDecimal(NumberToTextConverter.toText(n)).setScale(p, java.math.RoundingMode.DOWN).doubleValue();
 return retval;

I will provide a patch, unit tests and integration test FormulaEvalTestData.xls soon
Comment 1 Cédric Walter 2014-07-18 14:57:29 UTC
Created attachment 31829 [details]
Comment 2 Cédric Walter 2014-07-18 14:58:01 UTC
Created attachment 31830 [details]

Integration test
Comment 3 Cédric Walter 2014-07-23 10:07:11 UTC
this patch break the ROUNDDOWN method in "some" cases and should not be merged!

Patch require further analysis

BTW the rounddup function also do not behave in POI like in excel and will hopefully be solved soon.
Comment 4 Dominik Stadler 2019-03-10 16:40:03 UTC
There were some changes to the methods in the meantime, so I think this is mostly fixed now, if not, then please reopen this bug with updated information.

The latest fix was applied via r1801798: 
github #43: fix roundUp and roundDown. Thanks to @FishMeat on github for the patch.