0

NP Function handle error with IFERROR

Tom Eaton

April 12, 2018 14:39

Answered

Follow

Looking for some assistance using the NP function with an IFERROR function, please bare with me as I try to explain the issue I am getting.

Information:

1. NL Function is being used to generate a Table
2. One of the columns in this table uses an NP function to run an additional NL function
3. The NL function in the NP function runs perfectly fine but it does reference a NULL values on different rows resulting in a #VALUE! error in that particular row.

The null value is to be expected as this table does contain fields which are null/empty, if the field it references does contain a value it works perfectly.

How can I use Excel's IFERROR function to replace the #VALUE! with an empty value.

If would expect it to be like =NP("Formula","=IFERROR(=NL("......")," ")) but that didn't work for me. Any help would be greatly appreciated.

 

 

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Harry Lewis April 12, 2018 14:55

  Hi Tom -

  You might want to review the article on using Blank Filters.

  Let's look at a really simplistic (and non-realistic) example.

  I've used NL(Table) to create a list of customer info - including the state in which they are located and (for some inexplicable reason) the first customer I find who is also in that state. (like I said, not a realistic example).

  

  Because my data has a number of records where the State field is blank, when this report is run, I'm going to get some #VALUE! errors:

  

  In this case, I am using Dynamics NAV and can fix that by changing my filter to use the "@@" technique described in the article listed above:

  

  Now, when I run the report, it works as expected:

  

   

  Edited by Harry Lewis April 12, 2018 14:56
• 0
  
  Tom Eaton April 12, 2018 15:12

  That worked perfectly! Thank you so much and for the fast response.

  Unrelated question do you know what the NL, NP, NF stands for - it has been on ongoing question in our office :)

• 0
  
  Harry Lewis April 12, 2018 15:29

  Anymore?  Nothing.

  When the original version of "Jet Reports" was created, it worked solely with Navision.  That's where the N comes from.  The L, P, and F tended to refer to what each function (originally) did... create Lines, execute Procedures, or reference Fields.