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]
