pjfanning opened a new pull request, #1275: URL: https://github.com/apache/poi/pull/1275
**Behaviour change for 6.0.** Excel stores and calculates in IEEE 754 double precision and only exposes 15 significant digits; it compensates for binary representation error in a few well-defined places ([Floating-point arithmetic may give inaccurate result in Excel](https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result)). LibreOffice's interpreter implements the same model. POI's fix for bug 65792 instead rounded the operands of `*` and `/` to 15 digits and multiplied/divided as `BigDecimal`. That produced Excel's *displayed* result for that bug's case (`INT(880000000*0.00849/3)`) but diverged from what Excel computes — e.g. `(0.1+0.2)*10` became exactly `3` instead of `3.0000000000000004`, so downstream arithmetic Excel shows as non-zero came out as 0 — and it was slow (two `toText`, two `BigDecimal` parses and a `parseDouble` per operation). This makes evaluation match Excel: - **`*` and `/`** are plain IEEE 754. Subnormal results flush to 0 (Excel has no subnormals); overflow is `#NUM!` as before. Also removes an uncaught `ArithmeticException` for a subnormal divisor (`toText(Double.MIN_VALUE)` is `"0"`). - **`+` and `-`** return exactly 0 when the operands cancel to Excel, i.e. are equal at 15 significant digits (reusing `NumberComparer`) — the Excel 97+ correction Microsoft documents. `1.333+1.225-1.333-1.225` and `0.5-0.4-0.1` are 0; `(43.1-43.2)+1` stays `0.8999999999999986` (displayed `0.899999999999999`, as in the article). - **`INT`, `FLOOR`, `CEILING`, `FLOOR.MATH`, `CEILING.MATH`, `FLOOR.PRECISE`, `CEILING.PRECISE`** act on the 15-digit view of their argument, which is how Excel gets 2490400 from 2490399.9999999995. `ROUND`/`TRUNC` already did this. `INT` also no longer clamps at 2^63 (it used `Math.round`). - **`MOD`** is `n - d*INT(n/d)` as Excel defines it, falling back to the exact remainder when that overflows (where Excel gives `#NUM!`). New public helper `org.apache.poi.ss.util.ExcelArithmetic` (`approxValue`, `approxAdd`, `approxSub`), next to `NumberComparer`/`NumberToTextConverter`. Known deviation: Excel applies the near-zero correction only to the *last* operation of a formula (`=(0.5-0.4-0.1)*1` gives -2.78E-17 in Excel); like LibreOffice, this applies it to every addition/subtraction. Tests: expectations that the bug 65792 commit re-baselined to the `BigDecimal` results are restored to the IEEE values (`TestBuildFile` goes back to its pre-65792 value exactly; `TestExternalNameReference` back to the exact product). `TestNumericFunction` now asserts both the computed value and its 15-digit display. New `TestExcelArithmetic` and `TestTwoOperandNumericOperation`. Ran `ss.formula.*`, `ss.usermodel.*`, `ss.util.*`, `hssf.usermodel.*`, `poi-excelant` and the `poi-ooxml` `ss`/`xssf.usermodel`/`xssf.streaming` suites locally: no failures beyond three font-metric autosize tests that fail identically on trunk on macOS. Supersedes the `TwoOperandNumericOperation` fast path in #1271 (no longer needed). `changes.xml` to follow separately. 🤖 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]
