BYROW function

1 year ago

3 minutes

The BYROW function in Excel is a dynamic array function introduced in Excel 365 and Excel 2021. It allows you to apply a calculation to each row in a given range or array and return an array of results, one for each row. This is particularly useful when you want to perform row-wise operations without manually processing each row.

Syntax:

BYROW(array, lambda)

Arguments:

array: The range or array of data that you want to perform calculations on. This could be a range of cells or an array constant.
lambda: A LAMBDA function or expression that defines the calculation to be applied to each row in the array. The LAMBDA function will execute for each row individually.

Example:

Example 1: Sum of each row

Data (in cells A1:C3):
```
1   2   3
4   5   6
7   8   9
```
Formula:
```
=BYROW(A1:C3, LAMBDA(row, SUM(row)))
```
Result:
```
6   15   24
```
The formula calculates the sum of each row (1+2+3, 4+5+6, 7+8+9) and returns an array: [6, 15, 24].

Example 2: Average of each row

Data (in cells A1:C3):
```
1   2   3
4   5   6
7   8   9
```

Formula:

=BYROW(A1:C3, LAMBDA(row, AVERAGE(row)))

Result:
```
2   5   8
```
The formula calculates the average of each row (average of 1,2,3; average of 4,5,6; average of 7,8,9) and returns the result [2, 5, 8].

Example 3: Maximum value of each row

Data (in cells A1:C3):
```
1   2   3
4   5   6
7   8   9
```
Formula:
```
=BYROW(A1:C3, LAMBDA(row, MAX(row)))
```
Result:
```
3   6   9
```
The formula calculates the maximum value in each row and returns the array [3, 6, 9] (maximum of row 1, maximum of row 2, maximum of row 3).

Key Points:

Row-Wise Calculations: The BYROW function operates on each row of a range or array, applying the specified LAMBDA function to each individual row.
Dynamic Arrays: BYROW supports dynamic arrays, which means the result will “spill” into adjacent cells based on the number of rows in the array.
Custom Calculations: By using LAMBDA, you can define custom operations for each row, making this function versatile for a wide variety of tasks.
Efficient Processing: BYROW simplifies row-wise operations, reducing the need for manual processing or complex array formulas.

Use Cases:

Row-Based Calculations: Use BYROW to apply the same calculation to each row in a range. For example, calculating the sum, average, or maximum of each row:
```
=BYROW(A1:C10, LAMBDA(row, SUM(row)))
```
Custom Row Operations: If you need to perform custom logic across rows, you can use BYROW with LAMBDA to apply your custom formula:
```
=BYROW(A1:C3, LAMBDA(row, IF(MAX(row) > 5, "High", "Low")))
```
Dynamic Reports: When creating dynamic reports that require row-wise analysis or summaries, BYROW can help automate the process.

Post Views: 21