Welcome to Excel Jet Consult blog post. In this video, we look at using the new BYCOL and BYROW LAMBDA Helper Functions. If you enjoy this video, like share, comment and subscribe.
Video
Be sure to watch the video and subscribe to the YouTube channel
BYCOL and BYROW LAMBDA Helper Functions
BYROW Function applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
Syntax
=BYROW(array, lambda(row))
The BYROW function syntax has the following arguments:
- array An array to be separated by row.
- lambda A LAMBDA that takes a row as a single parameter and calculates one result. The LAMBDA takes a single parameter:
- row A row from array.
Syntax
=BYCOL (array, lambda(column))
The BYCOL function syntax has the following arguments and parameters:
- array An array to be separated by column.
- lambda A LAMBDA that takes a column as a single parameter and calculates one result. The LAMBDA takes a single parameter:
column A column from array
Functions Application
In the caption below, we want to calculate Grand Total at the row and column levels in column F and row 19 respectively.
To calculate the Grand Total at the row level, in cell F2,
Execute the formula: =BYROW(B2:E18,LAMBDA(byrow,SUM(byrow)))
Click Enter
From the above formula, range B2:E18 is the array to be separated by column. Then, we wrapped LAMBDA function inside the BYROW, We used byrow as the lambda parameter. In addition, we used SUM aggregate function to sum the passed in byrow parameter. This means that all values in range B2:E2 for instance, are summed and the result stays in cell F2. Importantly, the result spilled to the neighboring cell. This means, we don’t need to use the fill handle to drag down the formula!
From the caption below, we can see the result at the row level
To calculate the Grand Total at the column level, in cell B19,
Execute the formula: =BYCOL(B2:E18,LAMBDA(bycol,SUM(bycol)))
Click Enter
From the caption below, we can see the result at the column level
See you in the next blog post