The BYROW function in Excel is designed to apply a specified function to each row in an array or range and return an array of results, one for each row. This means that if you have a range of data with multiple rows, BYROW can process each row individually and return a result for each one. The beauty of BYROW is that it can work with both built-in Excel functions and custom functions created using LAMBDA. This makes it incredibly flexible and powerful.

Syntax of the BYROW Function

The BYROW function in Excel is designed to apply a specified function to each row in an array or range and return an array of results, one for each row. The syntax of the BYROW function is as follows:

=BYROW(array, lambda(row))

• array: This is the range or array of data that you want to process. It could be a range of cells, an array constant, or even a dynamic array returned by another function.
• lambda(row): This is a LAMBDA function that defines the operation to be applied to each row. The LAMBDA function takes a single parameter, row, which represents each row in the array. Inside the LAMBDA function, you can perform any calculation on the row.

Example 1: Summing Values in Each Row

Let’s start with a simple example. Suppose you have a sales dataset where each row represents a different product, and you have columns for the quantity sold and the price per unit. You want to calculate the total sales for each product. Here’s how your data might look:

To calculate the total sales for each product, you can use the BYROW function with a custom LAMBDA function that multiplies the quantity by the price per unit. Here’s the formula:

=BYROW(B2:C5, LAMBDA(row, INDEX(row, 1) * INDEX(row, 2)))

In this formula, B2:C5 is the array containing the quantity and price per unit for each product. The LAMBDA function takes each row and multiplies the second element (row[1]) by the third element (row[2]) to get the total sales for that product. The result is an array of total sales for each product, which spills into the cells below the formula.

Place the formula in a cell where you want the results to start (for example, cell D2).

The formula will return an array of results, where each result is the product of the two elements in each row. The results will spill into the cells below the cell where you entered the formula (in this case, D2:D5).

Example 2: Finding the Maximum Value in Each Row

Now, let’s look at a slightly more complex example. Suppose you have a dataset with multiple rows, and each row contains several numerical values. You want to find the maximum value in each row. Here’s how your data might look:

To find the maximum value in each row, you can use the BYROW function with the MAX function. Here’s the formula:

=BYROW(A11:D13, LAMBDA(row, MAX(row)))

In this formula, A11:D13 is the array containing the values. The MAX function is applied to each row, and the result is an array of maximum values for each row, which spills into the cells below the formula.

Place the formula in a cell where you want the results to start (for example, cell E11).

The formula will return an array of results, where each result is the product of the two elements in each row. The results will spill into the cells below the cell where you entered the formula (in this case, E11:E13).

Short Form “Eta Lambda” Syntax

One of the neat features of the BYROW function is that it supports a short-form “eta lambda” syntax for simple operations. This means that you can pass a function by name only into BYROW without needing to use LAMBDA. For example, to sum each row in an array, you can simply use:

=BYROW(array, SUM)

This formula is equivalent to the above long-form version:

The short-form syntax is concise and easy to read, making it a great choice for simple operations. However, it’s important to note that the short-form syntax only works for functions that accept a single argument, such as SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, etc. It won’t work for more complex operations that require custom logic.

Example 3: Counting Values Over a Certain Threshold

For our third example, let’s say you have a dataset with multiple rows, and each row contains several numerical values. You want to count how many values in each row are greater than a certain threshold, say 20. Here’s how your data might look:

=BYROW(A11:D13, LAMBDA(row, SUM(--(row>20))))

In this formula, A11:D13 is the array containing the values. The LAMBDA function takes each row (row) and creates a logical array where each element is TRUE if the value is greater than 20 and FALSE otherwise. The double negative (–) converts these logical values to 1s and 0s, and the SUM function adds them up to get the count of values greater than 20 for that row. The result is an array of counts for each row, which spills into the cells below the formula.

Example4: Applying MAX and MIN to Each Row

For our final example, suppose you have a dataset with multiple rows, and each row contains several numerical values. You want to find both the maximum and minimum values in each row. Here’s how your data might look:

To achieve this, you can use the BYROW function with HSTACK to stack the results of MAX and MIN horizontally for each row. Here’s the formula:

=BYROW(A2:D4, HSTACK(MAX, MIN))

• Array Range (A2:D4):
  • This is the range containing the numerical values for each row.
• HSTACK Function:
  • HSTACK(MAX, MIN)
    • MAX: This calculates the maximum value in the row.
    • MIN: This calculates the minimum value in the row.
    • HSTACK(MAX, MIN): This stacks the maximum and minimum values horizontally for each row.

Place the formula =BYROW(A2:D4, HSTACK(MAX, MIN)) in a cell where you want the results to start (for example, cell E2).

The formula will return an array of results, where each row contains the maximum and minimum values from the corresponding row in the input range. The results will spill into the cells to the right of the cell where you entered the formula (in this case, E2:F4).

And there you have it! The BYROW function is a powerful tool in your Excel arsenal that can help you process data row by row with ease. Whether you’re summing values, finding maximums, or counting values over a threshold, BYROW can handle it all. By leveraging the power of LAMBDA and the convenience of the short-form syntax, you can write more efficient and dynamic formulas that make your spreadsheets come alive.

Video: How To Use BYROW Function in Excel 365