Version: First seen with 3.10-FINAL, however skimming the code it appears to still be a problem in 3.15-FINAL. Excel 2016 added a new SWITCH statement, and likely others. Running evaluateAll() on the workbook results in the following stack trace. org.apache.poi.ss.formula.FormulaParseException: Name '_xlfn.SWITCH' is completely unknown in the current workbook at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:920) at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:558) at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:429) at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:268) at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1119) at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1079) at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1066) at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1426) at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1526) at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1510) at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1467) at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1447) at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1568) at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:176) at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getFormulaTokens(XSSFEvaluationWorkbook.java:148) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:286) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230) at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:264) at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:151) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:324) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:315) at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateAll(XSSFFormulaEvaluator.java:252) For anyone seeing this bug and looking for a workaround, follow the instructions at http://poi.apache.org/spreadsheet/user-defined-functions.html to implement a custom function with the name '_xlfn.SWITCH'.
Official documentation for SWITCH function: https://support.office.com/en-us/article/SWITCH-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e
I don't have Excel 2016 to be able to create a workbook to test this. Could you upload a valid, blank Excel 2016 file with a switch function and populate any cells necessary to verify the function evaluation is correct?
Created attachment 34878 [details] Example of 2016 SWITCH Statement According to https://www.onmsft.com/news/excel-2016-gains-6-new-functions-to-simplify-formula-editing , there are 6 new formulas. Here's the Microsoft documentation for each: SWITCH - https://support.office.com/en-us/article/SWITCH-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e IFS - https://support.office.com/en-US/article/IFS-function-36329a26-37b2-467c-972b-4a39bd951d45 CONCAT - https://support.office.com/en-US/article/CONCAT-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2 TEXTJOIN - https://support.office.com/en-US/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c MAXIFS - https://support.office.com/en-us/article/MAXIFS-function-dfd611e6-da2c-488a-919b-9b6376b28883 MINIFS - https://support.office.com/en-us/article/MINIFS-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599
Created attachment 34879 [details] Example of 2016 IFS Statement
Created attachment 34880 [details] Example of 2016 MAXIFS Statement
Created attachment 34881 [details] Example of 2016 MINIFS Statement
Created attachment 34882 [details] Example of 2016 TEXTJOIN Statement
Created attachment 34883 [details] Example of 2016 CONCAT Statement
Don't know why This bug is still on 'NEEDINFO'. I did already write an IFS and SWITCH function (and their Tests) Should I add the patch here?
(In reply to Pieter Degraeuwe from comment #9) > Don't know why This bug is still on 'NEEDINFO'. > > I did already write an IFS and SWITCH function (and their Tests) > Should I add the patch here? Please either attach the patch and unit tests here, or open a pull request against our Github mirror, whatever is easiest for you!
Created attachment 35955 [details] Patch for IFS and SWITCH Patch contains (I tink) working implementations for IFS and SWITCH please be gentle: I'm a newbie here at poi ;-) So, it could be that the implementations are not that great. Comments are welcome!
I've loaded with version 4.1.1 library. When I evaluateCell contain MINIFS, the NotImplemented error occur.
Apologise as I'm new to this, so this may be the wrong bug but there is also the new XLOOKUP function added in Excel 2019.
thanks Pieter - added patch using r1891856 will close this - additional functions will need new issues
added partial implementation of xlookup using https://github.com/apache/poi/pull/243 - still lots of scenarios to be done - if anything is intetested in helping out