Named Lambda Function Error Bug?

Copper Contributor

I am not sure if this is a bug, but it seems odd to me. Lets says you want to create a Lambda function which outputs "#N/A" under a certain condition by using the NA() function, i.e.:



If the "input" is selected to be {0,1} as contained by A2:A3, then the output is {#N/A,1}, as intended. Note here that for the cell containing "#N/A", Excel does not give the arrow in the top left corner of the cell that indicates "value not available error". 


However, if this lambda function is converted to a named formula and the same input values are given (i.e., {0,1}), then Excel again outputs {#N/A,1}, except the cell with #N/A has the error indicator in the top left corner indicating that the output is "not an available error" This seems inconsistent with the previous result. 


Finally, if the named  function is modified such that it also allows specification of the output if the input = 0, i.e.: 



then the error indicator no longer appears in the cell with the #N/A output. 


Perhaps this is intentional and I am not using these functions properly. However, having the error indicator appear on an intentional NA() output is contrary to the other uses of NA(). I prefer to use NA() as outputs as they are not plotted by graphs and hence other outputs are typically not useful for my purposes. 


Here is screenshot of the inputs (A2:A3) and outputs. 




3 Replies


That's simpler to test. Compare =LAMBDA( NA() )() in cell with function fn=LAMBDA( NA() ) called as fn().

Not sure why so.


It may be that the test for flagging #N/A as an error or the intended result of a formula may be completely superficial; if NA() appears in the formula, then it is assumed the user means it!

= LAMBDA([dummy], NA())

//"Worksheet formulae"
These are indicated as being errors
= TestNAλ("?")
= TestNAλ()
whilst this is not
= TestNAλ(NA())

In no case does the 'function' actually use the parameter.


@Peter Bartholomew 

That's actually second formula (with workaround) from @BRWNewc .

Looks like #N/A is handled bit differently from other error, Moreover, if we type in cell =NA() (or simply #N/A which is equivalent of =NA() ) it doesn't give error mark as any other error (e.g. =1/0).


I may only guess what is the logic behind. In any case we can't correct it, only take as it is.