BYCOL function

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: A LAMBDA that's applied to each column in the given array or range to obtain its grouped value.
  • Syntax: LAMBDA(name,formula_expression)
  • Requirements:
    • The LAMBDA must have exactly one name argument along with a formula_expression that uses that name. The name resolves to the current column being grouped when the LAMBDA is applied.

Notes

• The passed LAMBDA should accept exactly one name argument, otherwise an #N/A error 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 function can be passed for the LAMBDA parameter and behaves like a LAMBDA in this case. Learn more about named functions.
  • There should be exactly one 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:

Example: =BYCOL(A1:C3,LAMBDA(column,MAX(column)))

Result:

Returns the name of the sales representative with average sales greater than or equal to 30

Example data:

Example: =FILTER(B1:D1,BYCOL(B2:D4,LAMBDA(col,AVERAGE(col)>=30)))

Result:

Returns a 1x3 array with the difference between max and min value of each column using a named function as LAMBDA

Make a copy

 

Example data:

Example: =BYCOL(A1:C3,MAX_MIN_DIFF)

Named function: MAX_MIN_DIFF is a named function that 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:

Common errors

Related functions

• LAMBDA function: This function lets you create and return a custom function with a set of names and a formula_expression that 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 and use named functions: This function lets you create and store custom functions, similar to LAMBDA.