Bug 65467 - Excel function IFNA() is not defined
Summary: Excel function IFNA() is not defined
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 5.0.0-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2021-07-28 02:03 UTC by Ross Patterson
Modified: 2021-08-02 07:37 UTC (History)
0 users

patch_2021-07-29.tar.gz (10.35 KB, patch)
2021-07-29 16:33 UTC, Ross Patterson
Details | Diff
patch_site_2021-07-29.tar.gz (8.85 KB, patch)
2021-07-29 21:19 UTC, Ross Patterson
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Ross Patterson 2021-07-28 02:03:09 UTC
The Excel IFNA() function isn't defined in POI.  I needed it, so I whipped up an implementation, but I'm having problems with testing it.  I've got a unit test (TestIfna.java), built similarly to lots of other function tests, and it passes.  But my attempt to test using IFNA() in a spreadsheet (TestIfnaFromSpreadsheet.java) acts as if the function isn't defined:

   Testcase: org.apache.poi.ss.formula.functions.TestIfnaFromSpreadsheet
   Test: processFunctionRow(String, String, HSSFSheet, int, HSSFFormulaEvaluator, int)[1] took 140 milli sec(s) FAILED: In IfnaTestCaseData.xls Ifna!B5 {=_xlfn.IFNA(E5,F5)} 'An N/A value' - exception in evaluator.evaluate(evalCell): 
   org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Ifna!B5 with inner exception:       
   org.apache.poi.ss.formula.eval.NotImplementedFunctionException: _xlfn.IFNA
   org.apache.poi.ss.formula.eval.NotImplementedFunctionException: _xlfn.IFNA

This is a surprise, because I've updated FunctionEval.java and functionMetadata.txt to define it.  I followed the "Developing Formula Evaluation" instructions at https://poi.apache.org/components/spreadsheet/eval-devguide.html, but I've clearly missed a step.  Where should I be looking?  Is there a decent example of a patch that adds a new function?
Comment 1 Nick Burch 2021-07-28 08:24:11 UTC
Functions prefixed with _xlfn are newer functions since the file format was fixed, placed into an extension space, see

You need to list the implementation of these functions in AnalysisToolPak, as that handles the extension namespace

See http://svn.apache.org/viewvc?view=revision&revision=1887656 for a similar text function getting implemented

If you have a bit of time to write up some instructions for the eval devguide once you get it working, that'd be great!
Comment 2 Ross Patterson 2021-07-28 17:10:26 UTC
Ah!  I thought the AnalysisToolPak support was for the actual ATP-supplied functions (https://support.microsoft.com/en-us/office/use-the-analysis-toolpak-to-perform-complex-data-analysis-6c67ccf0-f4a9-487c-8dec-bdb5a2cefab6), not all post-2007 functions.  That was the hint I needed.

I'll take a crack at improving the devguide, while I'm still new enough to see the problems :-)
Comment 3 Ross Patterson 2021-07-29 16:32:04 UTC
A patch to implement IFNA() and add unit tests for it is attached as patch_2021-07-29.tar.gz.  Comments and criticism are very welcome.
Comment 4 Ross Patterson 2021-07-29 16:33:05 UTC
Created attachment 37974 [details]
Comment 5 PJ Fanning 2021-07-29 16:57:25 UTC
thanks - merged with r1891876
Comment 6 Ross Patterson 2021-07-29 21:19:45 UTC
Created attachment 37975 [details]

patch_site_2021-07-29.tar.gz contains an update for the eval-devguide.xml.  I made a lot of small stylistic changes, but the real gist of the changes are what was previously the "Walkthrough of an "evaluate()" implementation." section.  I also updated the list of supported functions in Appendix A.  I'm very open to criticism, as I'm a programmer, not a writer.
Comment 7 Dominik Stadler 2021-08-02 07:37:33 UTC
Applied the changed eval-guide via #1891958, thanks for the contributions here!