An average array formula across rows must return a row-wise average in Google Sheets. The AVERAGE function doesn’t expand its results.
Then how do we do that?
So to code an average array formula across rows, we must consider an alternative solution(s) in Google Sheets.
Why doesn’t the Average function expand its results?
An average (mean) is a single number representing an array or range of numbers.
If you use A2:C2 or A2:C3 (please see the table below) as the range to consider in AVERAGE, the formula would only return a single number to represent the range/array used.
| A | B | C | D | |
| 1 | Jan | Feb | Mar | Average |
| 2 | 75 | 75 | 75 | ? |
| 3 | 0 | 96 | 90 | ? |
For example, the formula =average(A2:C2) in cell D2 will return 75.
If you drag down this formula, you will get 62 in D3.
Can we use a single AVERAGE array formula in cell D2, which will expand down?
Yep! That’s what I meant by the title Average Array Formula Across Rows in Google Sheets.
The proper way to find the average across rows is by using the functions MMULT, QUERY, or DAVERAGE, in Google Sheets.
Update:- We can use BYROW now to expand AVERAGE across rows. I’ve added that solution also below.
Average Array Formula Across Rows Using MMULT – Regular Method
As you may know, the average is the sum divided by the count. So, I am following the below logic.
I’ll use one MMULT to sum across rows and another MMULT to count across rows. Both formulas will return array results.
Formula one divided by formula two will be the average array result across rows.
You will find two MMULT formulas for average calculation across rows below – one includes zero, and the other doesn’t.
For sample data, please refer to the GIF above.
Excluding Zero
The formula =ArrayFormula(mmult(N(B2:D5),sequence(columns(B2:D2),1)^0)), in cell F2, will return the sum of each row. But it has one limitation.
Note:- You can use transpose(sign(column(B:D)))) instead of sequence(columns(B2:D2),1)^0).
Generally, in the above MMULT, we can only use a fixed range like B2:D5 in the formula, not B2:D, an open array/range.
In one of my recent Google Sheets tutorials, I explained how to overcome this. Here is that tutorial – Proper Use of MMULT in Infinite Rows in Google Sheets.
I am following that approach here to modify the above formula.
Formula_1 (Sum):
=ArrayFormula(mmult(N(array_constrain(B2:D,MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(B2:D2),1)^0))Note:- This formula uses texts in A2:A to know the last row in the range and thus work in an open range. You can replace that with any other column.
Now, I hope it will be easy for you to get the count of each row.
To get that replace B2:D in the matrix1 part of formula_1 with if(B2:D>0,1,0). Enter the below code in G2.
Formula_2 (Count):
=ArrayFormula(mmult(N(array_constrain(if(B2:D>0,1,0),MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(B2:D2),1)^0))
Average Array Formula Across Rows (Excluding Zero):
Here is the generic formula.
=ArrayFormula(IFERROR({"Average Excluding Zero";sum/count}))And the final formula based on it.
=ArrayFormula(IFERROR({"Average Excluding Zero";mmult(N(array_constrain(B2:D,MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(B2:D2),1)^0)/mmult(N(array_constrain(if(B2:D>0,1,0),MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(B2:D2),1)^0)}))
Including Zero
To include zeros in the calculation of the average array formula across rows, you need to make only one change in the solution above.
In the above, replace if(B2:D>0,1,0) with if(len(B2:D),1,0).
=ArrayFormula(IFERROR({"Average Including Zero";mmult(N(array_constrain(B2:D,MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(B2:D2),1)^0)/mmult(N(array_constrain(if(len(B2:D),1,0),MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(B2:D2),1)^0)}))
QUERY and DAVERAGE Alternative Solutions – Regular Method
The QUERY formula is so simple. So I am skipping the explanation.
=query(A1:D,"Select (B+C+D)/3")This formula has limitations. It will include 0s in the mean calculation. Also, if any cell is blank, it won’t return the result in that row.
We can modify this QUERY formula to include blanks and include/exclude zeros. But that will make this Query complex.
I have another formula for this purpose that I would rate above MMULT – Average Each Row in Dynamic Range in Google Sheets.
Average Array Formula Across Rows Using BYROW LHF – New Method
We can now expand an AVERAGE formula by using the BYROW Lanmbda Helper Function (LHF) in Google Sheets.
It is the easiest solution to code an expanding average array formula in Google Sheets.
Use the AVERAGE function with BYROW when you want to include zero.
={"Average Including Zero";byrow(B2:D,lambda(row,iferror(average(row))))}To Exclude Zero in the BYROW Average Array Formula, use AVERAGEIF instead.
={"Average Excluding Zero";byrow(B2:D,lambda(row,iferror(averageif(row,">0"))))}That’s all. Thanks for the stay. Enjoy!
Hi Prashanth,
Thank you for the advice you provided in your article. I successfully used your formula (Including 0s).
What numbers do I need to change in this formula to calculate averages over more than three columns, let’s say five columns, for example?
Kind regards,
Kevin
Hi, Kevin,
I know you may have already tried replacing all B2:D with B2:F and B2:D2 with B2:F2 in the formula.
But that’s not enough!
You must also replace 3 in the Array_Constrain part of the formula with 5.
Also, there is a much simpler solution now using BYROW with AVERAGE. I’ve updated the post!
Thanks for your response. After following your suggestion, I found that my issue was not with the range references (which translate fairly directly when moved); instead, it was a failure to expand the number of columns for ARRAY_CONSTRAIN to match my data set.
Many thanks for your wonderful and helpful content.
Greetings,
Many thanks for this helpful post.
If the data I want to evaluate starts in row 11 with column headers and row 12 with data, what adjustments do I need to make for the formula to work?
Hi, DeLa,
To get the formula for your data range, please do as follows.
1. Create my sample sheet and apply the formula.
2. Insert 10 rows at the top.
3. Copy that formula to your data range.
Personally, I think it’s much simpler to use this formula:
=ARRAYFORMULA((A:A+B:B+C:C+D:D)/4)Hi, Josh,
The formula may break when you insert/delete columns.
Thank you! Worked great.
Hi Prashanth,
Thank you! That worked beautifully! Is there a way to keep the average to 2 decimal places?
Thanks again.
Emily
Hi, Emily,
Use ROUND() with the formula as below.
=ArrayFormula({"LA: Listening & Speaking Average";round(iferror(DAVERAGE(TRANSPOSE({sequence(rows(E2:E),1),E2:M}),
sequence(rows(E2:E),1),{IF(,,);IF(,,)})),2)})
Hi Prashanth,
Thank you for the quick reply!
Here is a link to my spreadsheet: — link removed by admin —
Please feel free to edit as you wish. The form is not public nor live.
Thank you again for your help!
Hi, Emily,
Thanks for sharing your sheet with Edit access.
I’ve inserted the below DAVERAGE in cell AC1.
={"LA: Listening & Speaking Average";iferror(ArrayFormula(DAVERAGE(TRANSPOSE({sequence(rows(E2:E),1),E2:M}),
sequence(rows(E2:E),1),{IF(,,);IF(,,)})))}
I’ll write the tutorial soon and let you know by reply below.
Another requirement is the IF logical test. I have converted the AD2:AD formula into an array formula. Please find that in cell AD1.
I hope you can follow the formulas and replace other similar formulas.
Hi Prashanth,
Thank you for this article. I work with a K-6 school and I’ve created a form to serve as an assessment our teachers fill out for students. I would love some advice on calculating the average across rows as the form responses come in.
By no means am I a developer, so I get a bit lost in the complex formulas you use here. I attempted to tailor the formula to my spreadsheet but it didn’t work.
Any advice would be much appreciated! I can share my document if you’d like.
Thanks!
Emily
Hi, Emily,
Feel free to share your sheet. Leave the link of your editable sheet (you can make a sample and share) via an answer to this thread.
I won’t publish the link.
In the sheet, explain what you want and also show me your hand entered (expected) result. I’ll try my best.