Table of Contents
The BYCOL function in Google Sheets allows users to easily organize and manipulate data by columns. By using this function, users can quickly and efficiently perform calculations, sorting, and filtering on specific columns within a spreadsheet. This feature is particularly useful for managing large datasets and streamlining data analysis processes. By utilizing the BYCOL function, users can save time and enhance the accuracy of their data management in Google Sheets.
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: 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 1×3 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 1×3 array with the difference between max and min value of each column using a Named function as LAMBDA
Make a Copy
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 argument
If 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.
The last parameter of BYCOL wasn’t a LAMBDA
If the last parameter of BYCOL wasn’t a LAMBDA, this error occurs:
“Argument must be a LAMBDA.”
Example:=BYCOL(C1:C4,4)
The LAMBDA passed to BYCOL was incorrect
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.
The application of LAMBDA on the input array doesn’t group each column to a single value
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.
Cite this article
stats writer (2024). How can I use the BYCOL function in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-bycol-function-in-google-sheets/
stats writer. "How can I use the BYCOL function in Google Sheets?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-bycol-function-in-google-sheets/.
stats writer. "How can I use the BYCOL function in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-bycol-function-in-google-sheets/.
stats writer (2024) 'How can I use the BYCOL function in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-bycol-function-in-google-sheets/.
[1] stats writer, "How can I use the BYCOL function in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I use the BYCOL function in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.