We now describe the following new LAMBDA-based worksheet functions: BYROW, BYCOL, REDUCE, MAP, SCAN, and MAKEARRAY. These functions are available only to Excel 365 users. Users of other versions of Excel can obtain similar capabilities via Real Statistics lambda functions.
BYCOL and BYROW Functions
The BYCOL and BYROW functions allow you to perform an operation on the columns or rows of an array. The syntax is as follows where R1 is an m × n array
BYCOL(R1, LAMBDA(col, expression)): returns a 1 × n row array with the operation defined by LAMBDA on each column of R1.
BYROW(R1, LAMBDA(row, expression): returns an m × 1 column array with the operation defined by LAMBDA on each row of R1.
Note that BYCOL passes a single parameter to LAMBDA and that parameter represents a column in R1. Similarly, BYROW passes a single parameter to LAMBDA that represents a row in R1.
Example 1: Use BYROW and BYCOL to find the largest value in each row and each column in range A2:C5 of Figure 1.
Figure 1 – BYROW and BYCOL
The largest value in each column is shown in range A7:C7 by placing the dynamic array formula =BYCOL(A2:C5,LAMBDA(col,MAX(col))) in cell A7. The largest value in each row is shown in range E2:E5 using the formula =BYROW(A2:C5,LAMBDA(row,MAX(row))).
Note that row and col in the above formulas can be replaced by any legal name in Excel. Thus, the first formula can be replaced by =BYCOL(A2:C5,x,MAX(x))) and the result will be the same.
The formula =BYCOL(R1,LAMBDA(col,SUM(col))) is equivalent to the Real Statistics formula =SUMCOL(R1) as described in Measures of Central Tendency.
REDUCE Function
This function is used to output a single value based on the values in an array. The syntax is as follows where R1 is an m × n array. Note that this time, two parameters (accumulator and value) are passed to LAMBDA.
REDUCE(init, R1, LAMBDA(accumulator, value, expression)) = the value of the accumulator after processing the value of all the elements in R1. accumulator is initialized with the value init and for each element in R1, the accumulator is updated using expression.
Example 2: Find the sum of squares of all the even elements in range B2:D4 of Figure 2.
Figure 2 – REDUCE function
Here, u serves as the accumulator and v is the value in each of the cells in B2:D4. Essentially, we get the sum 0 + 0 + 36 + 0 + 4 + 0 + 0 + 16 + 4.
Cells are processed row by row. We see that the formula =REDUCE(0,B2:D4,LAMBDA(u,v,IF(ISEVEN(v),u+v^2,0))) returns the value 16 + 4 = 20 and not 4, which would be the case if the cells were processed in column by column order.
SCAN Function
This function is similar to REDUCE, except that this time the output has the same size and shape as R1 and each element in the output is the then-current value of the accumulator. The syntax is
SCAN(init, R1, LAMBDA(accumulator, value, expression))
Example 3: Use the equivalent SCAN function in place of the REDUCE function for Example 2.
If we place the formula =SCAN(0,B2:D4,LAMBDA(u,v,IF(ISEVEN(v),u+v^2,u))) in cell G2, we obtain the results shown in Figure 3.
Figure 3 – SCAN function
MAKEARRAY Function
This function allows you to build an array by specifying the number of rows and columns and using LAMBDA to define the value of each element in the array. The syntax is
MAKEARRAY(nrow, ncols, LAMBDA(row, col, expression))
Example 4: Create a 3 × 4 array [aij] where aij = C(i+j, i)
The result is shown in Figure 4.
Figure 4 – MAKEARRAY function
MAP Function
This function is similar to MAKEARRAY, except that it maps a given array R1 into another array of the same size and shape based on the mapping defined by LAMBDA. The syntax is
MAP(R1, LAMBDA(element, expression))
Example 5: Create an array with the natural log of all the positive elements in range B2:C3 of Figure 5, mapping any non-positive elements to -1.
The result is shown in range B5:E6 of Figure 5 by inserting the formula shown in Figure 5 in cell B5.
Figure 5 – MAP function
Examples Workbook
Click here to download the Excel workbook with the examples described on this webpage.
References
Microsoft (2021) New lambda functions available in Excel
https://insider.office.com/en-us/blog/new-lambda-functions-available-in-excel
Microsoft (2021) MAP function
https://support.microsoft.com/en-us/office/map-function-48006093-f97c-47c1-bfcc-749263bb1f01