Problem using INDEX within MAX: WARNING: Incomplete code - cannot handle first arg of type (org.apache.poi.ss.formula.eval.NumberEval)

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Problem using INDEX within MAX: WARNING: Incomplete code - cannot handle first arg of type (org.apache.poi.ss.formula.eval.NumberEval)

Markus Kirsten
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem using INDEX within MAX: WARNING: Incomplete code - cannot handle first arg of type (org.apache.poi.ss.formula.eval.NumberEval)

pj.fanning
Thanks for the detailed error report.
Can you create an issue so that we can track this?
https://bz.apache.org/bugzilla/



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Problem using INDEX within MAX: WARNING: Incomplete code - cannot handle first arg of type (org.apache.poi.ss.formula.eval.NumberEval)

Yegor Kozlov-4
In reply to this post by Markus Kirsten
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 <[hidden email]> 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: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Problem using INDEX within MAX: WARNING: Incomplete code - cannot handle first arg of type (org.apache.poi.ss.formula.eval.NumberEval)

Markus Kirsten
Done, thank you for the identification of the problem. I’ve submitted the bug with the old description + a more brief description based on your email to https://bz.apache.org/bugzilla/show_bug.cgi?id=61859

> On 1 Dec 2017, at 15:30, Yegor Kozlov <[hidden email]> wrote:
>
> 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 <[hidden email]> 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: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]