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 <[email protected]> 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 <[email protected]> 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: [email protected] >> For additional commands, e-mail: [email protected] >> >> --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
