A lambda to simplify calculating N values in array expressions in place of using cumbersome IF(ISNUMBER) formulas.

Development Rationale

The purpose in developing this lambda is to allow the array non-compliant function N¹ to be used on arrays of values.  The N function returns a single value, being the numeric value of the first cell in a referenced range, so therefore cannot be used to return the numeric values of an array of values.  This function overcomes that limitation for an otherwise very handy function.

About the Lambda Calculations

1  =LAMBDA(array_of_values,SCAN(0,array_of_values,LAMBDA(cum,val,N(val))))

In line 1 above, the lambda is defined, with one argument, capitalised as is usual for Excel functions.  array_of_values is the array of values to which the N function is to be applied.  The lambda calls a SCAN function that simply returns the N value of each cell, and not utilising the capability to accumulate values over the scanned range.

Defined Name Settings

To create the lambda, simply use the define name² feature in Excel to define this named lambda in the workbook where it will be used.  The required settings are shown below and may simply be copied from here and pasted into the Define Name dialog box.

Syntax of λArrayOr

=λArrayN(array_of_values)

where:
   array_of_values is an array of values to which the N function is to be applied.

Sample File

An example file, showing the definition and use of this lambda is available here: Public Lambda Samples.xlsx.

 Version History

1  The N function returns the numeric value of a value.  It only works on the first value if an array is passed.  All numbers are returned unchanged, text values are returned as 0, logical value TRUE as 1 and the logical value FALSE as 0.  It has the same result as IF(ISNUMBER(x),x,0) except that the logical value TRUE would be 0 for that formula.  The correct equivalent to N would be =IFS(ISLOGICAL(x),1*x,ISNUMBER(x),x,TRUE,0).

²  The required settings are shown below.  Ctrl+Alt+F3 is the keyboard shortcut to open the New Name dialog, or you can select Formulas » Define Name.

³ You may change the name of the lambda and drop the prefix.  The prefix ensures there is no clash with functions that may be added by Microsoft later or with lambdas from another source.  The names given here also tie-in to the GitHub sources for these functions from the Clarkson ITT LAMBDA Library which can be downloaded using the Advanced Formula Environment.