Named Lambda Function Error Bug?
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.:
=Lambda(input,if(input=0,NA(),1))(A2:A3)
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.:
=Lambda(input,output,if(input=0,output,1))(A2:A3,NA())
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.
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.: =Lambda(input,if(input=0,NA(),1))(A2:A3)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.: =Lambda(input,output,if(input=0,output,1))(A2:A3,NA())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. Read More