This function groups an array by columns by application of a LAMBDA function to each column.
Sample Usage
BYCOL(A1:C3,LAMBDA(column,MAX(column)))
BYCOL(A1:C3,LAMBDA(column,SUM(column)))
Syntax
BYCOL(array_or_range,LAMBDA)
array_or_range: An array or range to be grouped by columns.LAMBDA: ALAMBDAthat’s applied to each column in the given array or range to obtain its grouped value.- Syntax:
LAMBDA(name,formula_expression) - Requirements:
- The
LAMBDAmust have exactly 1nameargument along with aformula_expressionwhich uses thatname. Thenameresolves to the current column being grouped when theLAMBDAis applied.
- The
- Syntax:
Notes
- The passed
LAMBDAshould accept exactly 1nameargument, otherwise an#N/Aerror is returned. This argument corresponds to a column in the input array. - Every column should be grouped to a single value. Array results for grouped values aren’t supported.
- A
named functioncan be passed for theLAMBDAparameter and behaves like aLAMBDAin this case. Learn more about named functions.- There should be exactly 1 argument placeholder defined for it.
- Parenthesis shouldn't follow the
named function.
Examples
Returns a 1x3 row-array with max of each column
Example data:
|
A |
B |
C |
|
|---|---|---|---|
|
1 |
3 |
5 |
7 |
|
2 |
4 |
3 |
5 |
|
3 |
1 |
2 |
4 |
Example: =BYCOL(A1:C3,LAMBDA(column,MAX(column)))
Result:
|
4 |
5 |
7 |
Returns the name of the sales representative with average sales greater than or equal to 30
Example data:
|
A |
B |
C |
D |
|
|---|---|---|---|---|
|
1 |
Ally |
Brian |
Lily |
|
|
2 |
2019 |
20 |
10 |
20 |
|
3 |
2020 |
50 |
15 |
30 |
|
4 |
2021 |
30 |
30 |
15 |
Example: =FILTER(B1:D1,BYCOL(B2:D4,LAMBDA(col,AVERAGE(col)>=30)))
Result:
|
Ally |
Returns a 1x3 array with the difference between max and min value of each column using a Named function as LAMBDA
Example data:
|
A |
B |
C |
|
|---|---|---|---|
|
1 |
3 |
5 |
7 |
|
2 |
4 |
3 |
5 |
|
3 |
1 |
2 |
4 |
Example: =BYCOL(A1:C3,MAX_MIN_DIFF)
Named function: MAX_MIN_DIFF is a named function which outputs the difference between the maximum value and the minimum value.
Formula definition: =MAX(col)-MIN(col), where col is an argument placeholder defined for MAX_MIN_DIFF.
Result:
|
3 |
3 |
3 |
Common Errors
The passed LAMBDA doesn’t have exactly 1 name argumentIf the passed LAMBDA doesn’t have exactly 1 name argument, and 1 formula expression as an argument, this error occurs:
“Wrong number of arguments to LAMBDA. Expected 1 argument, but got 2 arguments.”
Example: =BYCOL(C1:C4,LAMBDA(a,b,a+b))
In this example, LAMBDA was given 2 name arguments when it only needs 1.
If the last parameter of BYCOL wasn’t a LAMBDA, this error occurs:
“Argument must be a LAMBDA.”
Example: =BYCOL(C1:C4,4)
If 1 or more name arguments weren’t valid, this error occurs:
“Argument 1 of function LAMBDA is not a valid name.”
Example: =BYCOL(C1:C4,LAMBDA(C1,C1+1))
In this example, C1 is an invalid name since it clashes with a range.
If the application of LAMBDA on the input array doesn’t group each column to a single value, this error occurs:
“Single value expected. Nested array results are not supported.”
Example: =BYCOL(C1:C3,LAMBDA(col,col))
Related functions
- LAMBDA function: This function lets you create and return a custom function with a set of
namesand aformula_expressionthat uses them. - MAP function: This function maps each value in the given arrays to a new value.
- REDUCE function: This function reduces an array to an accumulated result.
- BYROW function: This function groups an array by rows.
- SCAN function: This function scans an array and produces intermediate values.
- MAKEARRAY function: This function creates a calculated array of specified dimensions.
- Create & use named functions: This function lets you create and store custom functions, similar to
LAMBDA.