poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Yegor Kozlov <yegor.koz...@dinom.ru>
Subject Re: Problem using INDEX within MAX: WARNING: Incomplete code - cannot handle first arg of type (org.apache.poi.ss.formula.eval.NumberEval)
Date Fri, 01 Dec 2017 14:30:59 GMT
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
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message