pjfanning opened a new pull request, #1322:
URL: https://github.com/apache/poi/pull/1322
**Depends on #1321** (uses its per-token array-mode flags) — merge that
first and I'll rebase; the second commit is this change.
## The bug
`=SUMPRODUCT(IF(A1:A3>1,B1:B3,0))` with A = 1,2,3 and B = 10,20,30 evaluated
to **0**. Excel gives **50** (the IF is evaluated element-wise inside
SUMPRODUCT → `{0,20,30}`), and so does POI when the same formula is entered as
an array formula.
What happened: the condition `A1:A3>1` is correctly evaluated element-wise
(its result feeds an `ArrayMode` function), but the optimised-IF shortcut
(`tAttrIf`) then took that *array* and reduced it to a single value with the
formula cell's coordinates — FALSE — and jumped to the `0` branch. The shortcut
is already disabled for array-formula cells, which is why the CSE version
worked.
## The fix
`findArrayModeOperands` also flags the `tAttrIf`/`tAttrSkip` tokens of an IF
whose result feeds an `ArrayMode` function (the attribute's operand is on top
of the structural stack; its consumer is the IF). For those the evaluator
neither takes the shortcut nor honours the jumps, so `IfFunc` receives all its
arguments and — being an `ArrayFunction` in array mode — evaluates
element-wise. Skips that belong to a `CHOOSE` are explicitly left alone. An IF
that does not feed an ArrayMode function is evaluated exactly as before.
## Tests
`TestArrayModeOperands.ifFeedingAnArrayModeFunctionIsEvaluatedElementWise`:
the bug's formula (50), IF without a false branch, `IF(...)*B1:B3`,
`INDEX(IF(...),1)`, nested IFs, a scalar condition selecting a whole branch (60
/ 0), the shortcut still used outside array functions (`IF(A1:A3>1,B2,0)` on
row 2 = 20), and CHOOSE next to and inside an array-mode IF.
Two adjacent things I noticed and left alone:
`SUMPRODUCT(CHOOSE(2,A1:A3,B1:B3))` gives 20 (CHOOSE reduces its chosen
argument to a single value; Excel gives 60), and legacy Excel without CSE would
have given 60 for the bug's formula — this PR follows current Excel.
Locally green: `ss.formula.*` (both modules), `TestHSSFFormulaEvaluator*`,
`TestFormulaEvaluatorBugs`, `TestBugs`, `ss.usermodel.*`,
`TestXSSFFormulaEvaluator*`, `TestXSSFXLookupFunction`,
`TestFormulaEvaluatorOnXSSF`, `TestMultiSheetFormulaEvaluatorOnXSSF`,
`TestXSSFBugs` (pre-existing `stackoverflow23114397` aside).
`changes.xml` left for you (user-visible fix).
🤖 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]
