The problem is not in the INDEX function, but in how the arguments are
evaluated. POI does not fully support array operands and collapses the
expression ($B$2:$B$11=F2)*$A$2:$A$11 into a number while INDEX expects an
array, i.e. the INDEX arguments are wrong. Instead of evaluating
INDEX({1,2,2,3,0,0,0,4,0,0}, 0)
POI evaluates it as
INDEX(1, 0)
Can you create a bug report and attach the file? It is certainly an area to
improve.
On Thu, Nov 30, 2017 at 4:33 PM, Markus Kirsten <mkirsten@gmail.com> wrote:
> Hi,
> For a spreadsheet I am trying to update and evaluate with POI I am having
> some difficulties. The problem I am trying to solve in its simplest form is
> the following; From a data sheet with two columns - one with a numeric
> value, and one with a group, I would like to find the maximum value for
> each group. So from the table below, I’d like to calculate that max for A
> is 4, max for B is 10 and max for C is 20.
>
> Value Group
> 1 A
> 2 A
> 2 A
> 3 A
> 5 B
> 3 B
> 10 B
> 4 A
> 10 C
> 20 C
>
> One way to do this would be to create the following table, where the
> column max value is to;
>
> =MAX(INDEX(($B$2:$B$11=F2)*$A$2:$A$11;0))
>
> The logic is basically; find where column B (the group) matches the
> specified group. This is an array with 0/1s. Multiply that array with the
> value array. Now we have an array with either 0s or the values from the
> specified group. Take max of that.
>
> Group Max value
> A 4
> B 10
> C 20
>
> However, when evaluating the formula above, I get the following error. I
> have uploaded a minimal test file on https://ufile.io/z6qg4 The 3 red
> cells are the only ones with formulas and it is when I evaluate them that I
> get this error.
>
> WARNING: Incomplete code - cannot handle first arg of type
> (org.apache.poi.ss.formula.eval.NumberEval) for cell G4
> java.lang.RuntimeException: Incomplete code - cannot handle first arg of
> type (org.apache.poi.ss.formula.eval.NumberEval)
> at org.apache.poi.ss.formula.functions.Index.
> convertFirstArg(Index.java:106)
> at org.apache.poi.ss.formula.functions.Index.evaluate(
> Index.java:50)
> at org.apache.poi.ss.formula.functions.Index.evaluate(
> Index.java:114)
> at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(
> OperationEvaluatorFactory.java:132)
> at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(
> WorkbookEvaluator.java:523)
> at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(
> WorkbookEvaluator.java:290)
> at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(
> WorkbookEvaluator.java:232)
> at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.
> evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:65)
> at org.apache.poi.ss.formula.BaseFormulaEvaluator.
> evaluateFormulaCellEnum(BaseFormulaEvaluator.java:192)
> at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(
> DataFormatter.java:887)
>
> Let me know if helpful with anything additional. Also happy to rewrite the
> Excel formula, if I can get it to work. Running Apache POI 3.17 on macOS.
>
>
> Many thanks in advance,
> Markus
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
|