You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Yegor Kozlov <ye...@dinom.ru> on 2017/12/01 14:30:59 UTC
Re: Problem using INDEX within MAX: WARNING: Incomplete code - cannot
handle first arg of type (org.apache.poi.ss.formula.eval.NumberEval)
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 <mk...@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
>
>
Re: Problem using INDEX within MAX: WARNING: Incomplete code - cannot
handle first arg of type (org.apache.poi.ss.formula.eval.NumberEval)
Posted by Markus Kirsten <mk...@gmail.com>.
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 <ye...@dinom.ru> 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 <mk...@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
>>
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org