pjfanning opened a new pull request, #1282: URL: https://github.com/apache/poi/pull/1282
**Behaviour change for 6.0.** Two evaluation divergences from Excel found while re-reviewing the arithmetic after #1275/#1278/#1280/#1281. ### 1. `=-2^2` evaluated to -4; Excel gives 4 Excel's [operator precedence](https://support.microsoft.com/en-us/office/calculation-operators-and-precedence-in-excel-48be406d-4975-4d31-b2b8-7af9e0e2878a) puts negation (`–`) above `%` and `^`, so `-2^2` is `(-2)^2 = 4`. `FormulaParser.parseUnary` parsed its operand with `powerFactor()`, giving `-(2^2)`. It now parses a `percentFactor()`, and the enclosing `powerFactor()` applies any `^` to the signed operand (one-line change). Token streams written by Excel already have this order — `tNum(-2) tInt(2) tPower` — so `.xls`/`.xlsx` files from Excel containing such formulas now evaluate as Excel does; only text parsed by POI was affected. `FormulaRenderer` needs no change (it only emits parentheses where `tParen` tokens exist). Unchanged, and now covered by tests: `2^-2` → 0.25 (sign in the exponent), `2^-2^2` → 0.0625, `-2%` → -0.02, `-2%^2` → 0.0004, `2^200%` → 4, `0-2^2` → -4, `-(2^2)` → -4, `-2^2^3` → 64 (left-associative). ### 2. `(-27)^(1/3)` evaluated to -3; Excel gives `#NUM!` `PowerEval` special-cased a negative base with `|exponent| < 1` (bug 62121, 2018) to return the negated real root. Excel returns `#NUM!` for a negative base with a non-integer exponent — the same as POI's own `POWER(-27,1/3)`, so the operator and the function disagreed with each other. The -3 is what LibreOffice returns, which is probably where the 62121 expectation came from. The special case is removed: `Math.pow` gives NaN there and `TwoOperandNumericOperation` already maps that to `#NUM!`. `TestPowerEval.testNegativeDecimalValues` and `testInSpreadSheet` are inverted accordingly; integer exponents (`(-3)^3` = -27, `(-2)^-2` = 0.25) are unaffected. Note that with 1 fixed, `=-27^(1/3)` is also `#NUM!` (it is `(-27)^(1/3)`); `=-(27^(1/3))` and `=0-27^(1/3)` give -3. Tests: `TestFormulaParser.testPrecedenceAndAssociativity` gains the token-order cases; `TestPowerEval.testNegationBindsTighterThanPower` evaluates 27 formulas end to end. Ran `ss.formula.*`, `hssf.model.*`, `hssf.usermodel.*Formula*`, `ss.usermodel.*Formula*` in `poi` (2817 tests) and `xssf.usermodel.*Formula*`, `ss.formula.*`, `xssf.streaming.*Formula*` in `poi-ooxml` (578 tests) locally: 0 failures. 🤖 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]
