Excel functions / Logical
BYCOL function
Runs a function on each column of an array and returns one result per column.
Syntax
=BYCOL(array, lambda)
Arguments
array: The array to go through, a column at a time.lambda: A LAMBDA with one input, the column. A function name such as SUM also works.
When to use it
BYCOL runs a LAMBDA on each column of a range and spills one result per column across: a total row, an average row or any calculation of each column, in one formula.
Examples
The total of each quarter
A B C 1 Q1 Q2 Q3 2 10 20 30 3 40 50 60 4 5 50 70 90 In A5:
=BYCOL(A2:C3, LAMBDA(col, SUM(col)))→ 50, 70, 90The average, by name
A B C 1 Q1 Q2 Q3 2 10 20 30 3 40 50 60 4 5 25 35 45 In A5:
=BYCOL(A2:C3, AVERAGE)→ 25, 35, 45
Common mistakes
- One value per column. The LAMBDA must give a single value for the column.