We can use nested BYROW functions to loop a row-by-row average in Google Sheets. This typically involves two tables: one table contains the values to average and the other table contains the conditions to apply in each loop.

I am talking about ‘true’ looping, not just talking about looping (iterating) row by row as the regular BYROW or other lambda functions do.

My nested BYROW approach is simple, but the important part here is to understand the concept of looping a row-by-row average.

Let me clarify it first.

In the average range C2:F8, we can use the following BYROW formula in cell H2 to iterate through each row and produce the above result in H2:H8:

=BYROW(C2:F8,LAMBDA(row,AVERAGE(row)))

Where:

• C2:F8 is the array or range of average values.
• AVERAGE(row) is the formula expression that calculates the average of a row.
• row is the name that resolves to the current row being grouped when the LAMBDA is applied.

Syntax of the BYROW Function: BYROW(array_or_range,LAMBDA(name, formula_expression))

This is equivalent to dragging the AVERAGE formula =AVERAGE(C2:F2) in cell H2 down to H3:H8.

We have already learned this in the array formula approach in my tutorial here: Average Array Formula Across Rows in Google Sheets.

Nesting the BYROW function to loop a row-by-row average is typically used for conditional row-by-row average calculations in Google Sheets.

Basic Example of a Conditional Row-by-Row Average in Google Sheets

In the table (Table #1) above, I want to find the average of values greater than 50. For that, we need to replace AVERAGE with AVERAGEIF or AVERAGEIFS. (We can use IF logical test or FILTER also).

I would prefer AVERAGEIFS because that will come in handy when we code the nested BYROW formula to loop a row-by-row average in Google Sheets.

Syntax of the AVERAGEIFS Function:

AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])

To find the average if the values are >50, we can use the following AVERAGEIFS function in cell H2 and drag it down:

=AVERAGEIFS(C2:F2,C2:F2,">50")

Where:

• C2:F2 is the average_range and criteria_range1
• ">50" is the criterion1

We can also use the BYROW function to return the conditional row-by-row average. Here is that formula:

=BYROW(C2:F8,LAMBDA(row,AVERAGEIFS(row,row,">50")))

Where:

• C2:F8 is the array or range of average values.
• AVERAGEIFS(row,row,">50") is the formula expression that calculates the conditional average of a row.
• row is the name that resolves to the current row being grouped when the LAMBDA is applied.

You can simply insert the formula in cell H2 and it will automatically spill down the range.

Nested BYROW to Loop a Row-by-Row Average in Google Sheets

Here is how to use nested BYROW to loop a row-by-row average in Google Sheets, or more specifically, a conditional row-by-row average.

So there will be two steps:

1. Conditional Row-by-Row Average.
2. Looping the Conditional Row-by-Row Average Using Nested BYROW.

For that, we will use two tables. You already have Table #1, which we used in the above examples. Table #1 contains the cumulative sales of 7 products in Q1, Q2, Q3, and Q4. We have learned how to find the row-by-row average and conditional average of each product in these four quarters.

What about the second table?

To perform a conditional row-by-row average based on Table #1, we can use a second table with just one row. For example, we could use the following table:

1. Conditional Row-by-Row Average with Two Tables

Assume the above boolean values are in C12:F12, which represents four quarters. What does the following formula do then?

=AVERAGEIFS(C2:F2,$C$12:$F$12,TRUE)

If you insert this formula in cell H2 and drag it down, it will return the row-by-row conditional average. It means it will return the mean of Q1 and Q2 in all rows because only these two columns in the corresponding row in Table #2 contain TRUE.

For your information, $C$12:$F$12 is an absolute reference, so the formula will always use this range, even if you drag the AVERAGEIFS formula down.

Here is the BYROW version of it, which will expand its own:

=BYROW(C2:F8,LAMBDA(row,AVERAGEIFS(row,C12:F12,TRUE)))

Only go to the next step if you understand this thoroughly.

2. Looping the Conditional Row-by-Row Average Using Nested BYROW.

In the last example, the second table contains values in C12:F12. Here we have values in C12:F15.

We need a formula that returns the average of all items in Q1 and Q2 in H2:H8, Q2 and Q3 in I2:I8, Q3 and Q4 in J2:J8, and Q1 and Q4 in K2:K8 based on the checkboxes checked in the second table.

Instead of using four conditional row-by-row formulas in H2, I2, J2, and K2, respectively, we can use one nested BYROW formula in cell H2 to loop a row-by-row average.

Here are the four formulas for your reference:

=BYROW(C2:F8,LAMBDA(row,AVERAGEIFS(row,C12:F12,TRUE)))  // H2 formuila
=BYROW(C2:F8,LAMBDA(row,AVERAGEIFS(row,C13:F13,TRUE)))  // I2 formuila
=BYROW(C2:F8,LAMBDA(row,AVERAGEIFS(row,C14:F14,TRUE)))  // J2 formuila
=BYROW(C2:F8,LAMBDA(row,AVERAGEIFS(row,C15:F15,TRUE)))  // K2 formuila

The following formula in cell H2 will return the average of the values in the specified columns, based on the checkboxes checked in the second table:

=TRANSPOSE(
   BYROW(C12:F15,LAMBDA(criteria, TOROW(
         BYROW(C2:F8,LAMBDA(row,IFERROR(AVERAGEIFS(row,criteria,TRUE),0)))
   )))
)

Explanation

The nested BYROW formula works as follows:

1. The outer BYROW function iterates over the rows of the second table, from C12 to F15.
2. For each row, the inner BYROW function iterates over the rows of the data table (Table #1), from C2 to F8.
3. For each row in Table #1, the inner BYROW function calculates the average of the values in the specified columns, based on the checkboxes checked in the second table.
4. The TOROW function converts the output of the inner BYROW function from a column to a row. This is a key step for the nested BYROW to work properly.
5. The TRANSPOSE function transposes the output of the outer BYROW function so that the results are returned in a row format.

The IFERROR function is used to return 0 if the second table contains a row with all unchecked checkboxes. This prevents the formula from returning an error.