pjfanning opened a new pull request, #1298: URL: https://github.com/apache/poi/pull/1298
Review of the remaining `BigDecimal` use in formula evaluation. `ROUND`/`ROUNDUP`/`ROUNDDOWN` (`MathX.round`) were already right — they build the decimal from `NumberToTextConverter`, i.e. Excel's 15-digit view. The others built it from the raw double where Excel does not: | Formula | Before | After (Excel) | |---|---|---| | `=FLOOR(0.9,0.1*3)` (also `FLOOR.MATH`, `FLOOR.PRECISE`) | `0.6` | `0.9` | | `=CEILING(1.6,0.7+0.1)` (also `CEILING.MATH`, `CEILING.PRECISE`) | `2.4` | `1.6` | | `=MROUND(0.45,0.1*3)` | `0.3` | `0.6` | | `=DOLLAR(2.675,2)`, `=DOLLAR(1.005,2)` | `$2.67`, `$1.00` | `$2.68`, `$1.01` | | `=DOLLAR(1550,-2)` | `$1,500` | `$1,600` | | `=DOLLAR(1,-400)` | `NumberFormatException` | `$0` | | `=FIXED(0.1+0.2,17)` | `0.30000000000000004` | `0.30000000000000000` | | `=FIXED(1,128)` | 128 places | `#VALUE!` (as `DOLLAR` already did) | | `=FIXED(1,1E9)`, `=PERCENTRANK(A2:A11,8,1E9)` | `ArithmeticException: BigInteger would overflow supported range` | `#VALUE!`; `0.666…` | - New `ExcelArithmetic.toBigDecimal(double)` (`@since 6.0.0`): the exact 15-significant-digit decimal Excel shows for a value (`0.1*3` → `0.3`, `2.675` → `2.675`). - `MathX.scaledRoundUsingBigDecimal` (the six FLOOR/CEILING variants) and `MROUND` build both the number and the significance/multiple from it. #1275 had already put the number on the 15-digit view; the significance was still the raw double, so `0.9/0.30000000000000004` came out just under 3. - `DOLLAR` rounds the decimal `HALF_UP` (Excel rounds half away from zero) and formats the `BigDecimal`, so the binary value never shows through the currency `DecimalFormat`'s half-even rounding. Negative places round instead of truncating and no longer go through `Math.pow`. - `FIXED` builds from the 15-digit view and formats the `BigDecimal`; places over 127 are `#VALUE!`. - Both cap a hugely negative place count at −400 (rounding to a multiple of 10⁴⁰⁰ can only give 0 for a double), and `PercentRank.round` caps the significance at 20, so `setScale` is never asked for a number with billions of digits. One assumption to flag: Excel's 127-place limit for `FIXED` is inferred from `DOLLAR` (where POI already enforced it); if `FIXED` differs I'll adjust. Tests: `TestExcelArithmetic.testToBigDecimal` plus cases in `TestFloor`, `TestCeiling`, the four `.MATH`/`.PRECISE` tests, `TestMRound`, `TestNumericFunction.testDOLLAR`, `TestFixed`, `TestPercentRank`; `org.apache.poi.ss.formula.*` passes. 🤖 Generated with [Claude Code](https://claude.com/claude-code) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
