Table of Contents
The BYROW function in Google Sheets allows users to organize and manipulate data in a row-by-row manner. By using this function, users can easily perform calculations and operations on multiple rows of data at once, instead of having to manually apply the formula to each individual row. This function is particularly useful for tasks such as creating sum or average calculations across multiple rows, or applying a specific formula to a large dataset. It simplifies data manipulation, making it more efficient and saving users valuable time and effort.
BYROW function
This function groups an array by rows by application of a LAMBDA function to each row.
Sample Usage
BYROW(A1:C3,LAMBDA(row,MAX(row)))
BYROW(A1:C3,LAMBDA(row,SUM(row)))
Syntax
BYROW(array_or_range,LAMBDA)
array_or_range: The array or range to group by rows.LAMBDA: ALAMBDAthat’s applied to each row 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 row 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 row in the input array. - Every row 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
Sum row-wise
Example Data:
A | B | C | |
|---|---|---|---|
1 | 3 | 5 | 7 |
2 | 4 | 3 | 5 |
3 | 1 | 2 | 4 |
Example:=BYROW(A1:C3,LAMBDA(row,SUM(row)))
Result:
15 |
12 |
7 |
Filters years where any quarterly sales exceeded 1300
Example data:
A | B | C | D | E | |
|---|---|---|---|---|---|
1 | Q1 | Q2 | Q3 | Q4 | |
2 | 2019 | 1060 | 295 | 1425 | 280 |
3 | 2020 | 270 | 585 | 675 | 170 |
4 | 2021 | 285 | 1200 | 780 | 1235 |
5 | 2022 | 1440 | 1390 | 45 | 650 |
Example:=FILTER(A2:A5,BYROW(B2:E5,LAMBDA(row,MAX(row)>1300)))
Result:
2019 |
2022 |
Returns a 3×1 array with the difference between max and min value of each row 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:=BYROW(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(row)-MIN(row), where row is an argument placeholder defined for MAX_MIN_DIFF.
Result:
4 |
2 |
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:=BYROW(C1:C4,LAMBDA(a,b,a+b))
For this example, LAMBDA was given 2 name arguments when it only needs 1.
The last parameter of BYROW wasn’t a LAMBDA
If the last parameter of BYROW wasn’t a LAMBDA, this error occurs:
“Argument must be a LAMBDA.”
Example:=BYROW(C1:C4,4)
The LAMBDA passed to BYROW 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:=BYROW(C1:C4,LAMBDA(C1, C1+1))
For 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 row to a single value
If the application of LAMBDA on the input array doesn’t group each row to a single value, this error occurs:
“Single value expected. Nested array results are not supported.”
Example: =BYROW(C1:E1,LAMBDA(row,row))
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.
- BYCOL function: This function groups an array by columns.
- 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 BYROW function in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-byrow-function-in-google-sheets/
stats writer. "How can I use the BYROW function in Google Sheets?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-byrow-function-in-google-sheets/.
stats writer. "How can I use the BYROW function in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-byrow-function-in-google-sheets/.
stats writer (2024) 'How can I use the BYROW function in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-byrow-function-in-google-sheets/.
[1] stats writer, "How can I use the BYROW function in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I use the BYROW function in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.