A lambda to simplify calculating AND conditions row-by-row or column-by-column in array expressions in place of using Product of Comparison..

Development Rationale

The purpose in developing this lambda is to simplify the derivation of logical and conditions in array calculations based on AND logic.  The AND function processes all comparisons in a single step, which isn't what is required when processing is to be carried out row-by-row or column-by-column.  The process can be undertaken using Product of Comparison type calculations, but they can become difficult to interpret within a complex array calculation, and necessitate using lots of parentheses to force the evaluations to be undertaken in the correct order.  This function is designed to simplify this process and allow the user to focus on the logic.  It is useful with all sorts of array calculations, but especially FILTER, GROUPBY and PIVOTBY.

About the Lambda Calculations

This lambda allows the passing of multiple logical comparisons.  An earlier version of this function allowed for up to five sets of comparisons.  However, it added some processing overhead and limited the number of conditions.  Although more than five might not be needed often, the new design allows the user to stack multiple logical arrays using HSTACK or VSTACK.  A separately calculated array of conditions can also be passed.

With the advent of the eta-reduced lambda feature, the logic in this lambda has been stripped back significantly, and yet allows for the design of clean filtering statements.  Due to the reduction in steps, the LET function is no longer required.

Here, I will layout the workings using line wrapping, so each step of the function can be read and analysed:

1  =LAMBDA(logical_arrays,[by_col],
2    IF(by_col,BYCOLUMN(logical_arrays,AND),BYROW(logical_arrays,AND)))

In line 1 above, the lambda is defined, with two arguments, capitalised as is usual for Excel functions.  logical_arrays is the set of arrays to be checked for AND conditions, and the optional argument by_col may take TRUE or a non-zero value (1 preferred if using numbers) to signify that the evaluation should occur by column.

In line 2, the result of the lambda is returned, being the set array of results returned by the IF function.  If processing is set to by_col, then an array of values one row deep by the number of columns passed will contain TRUE or FALSE dependent upon whether all the values in each column are TRUE or non-zero.  If by_col is omitted or the value FALSE or 0 is passed, processing is by row, then an array of values one column wide the number of rows passed will contain TRUE or FALSE dependent upon whether all the values in each row are TRUE or non-zero.

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 λArrayAnd

=λArrayAnd(logical_arrays,[by_col])

where:
   logical_arrays is a set of stacked arrays (created by HSTACK or VSTACK or from an array otherwise derived) of values to be evaluated using a logical AND, row-by-row or column-by-column.

   [by_col] is an optional argument to indicate processing should be by column.  The values TRUE or 1 may be used.  If omitted, processing defaults to by row.

Sample File

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

 Version History

¹  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.