Array Formula to Return Average of Every N Cells in Google Sheets

How to return the average of every n cells in a column in Google Sheets?

Under one of my posts, in the comment section for readers, one of my readers asked me about the possibility of writing an array formula to return the average of every n cells in a column.

He wants a single formula in one cell that will populate the average of every n cells in an adjoining column.

He doesn’t want to write an Apps Script for this may be to improve/enhance the performance.

The tutorial, the post contains the said comment, is how to Find the Average of the Last N Values in Google Sheets.

As an answer to his comment, I thought about writing a tutorial instead of just giving him a complex formula as a reply in the comments.

First, see (GIF below) what I want to say about returning the average of every n cells using an array formula in Google Sheets.

Average every n cells using Array Formula in Google Sheets — image # 1

There is an array formula in cell F2 (that you will get the end of this tutorial) which returns the average of every 1, 2, 3, 4, 5… cells as per the n in cell H2.

Needless to say, the value in cell H2 controls the n in the formula in cell F2.

Do you want this formula or a formula that simply returns the average without blank cells below each average as per the ‘n’ in another column?

In this tutorial, step-by-step you can learn to write the formula to return the average of every n cells in Google Sheets.

So you will get both the type of formulas – formula with or without blank cells as per n.

Average Every N Cells in Google Sheets – Step by Step Instructions

For writing the formula, we can use the values in column E from the example above as the sample data.

The title of the column is “test” in cell E1 and the values to average in every n cells are just below it (E2:E).

We can use a variable to refer to ‘n’. If we hard-code it in the formula, then we can’t easily switch from the average of every n cells. In my example, I am using cell H2 to refer to n (as the ‘n’).

If cell H2 contains 2, the formula would return the average of every two cells, if it contains 3, then the formula would return the average of every 3 cells and so on. Now let’s go to step 1.

Re-Arrange a Single Column into Multiple N Columns – Step 1

I have a formula in cell I2 which adjusts 1 column (E2:E) to n (H2) number of columns.

Step 1 Formula:

=ArrayFormula(IFERROR(hlookup("test",E1:E,SEQUENCE(roundup(COUNTA(E1:E)/H2),H2,2),0)))

Step 1 - Re-Arranging Data as Per N — *image # 2*

If we change the value in cell H2 the formula would adjust the number of columns accordingly.

Since the current value in cell H2 is 3, there are three columns in the output. If we change it to 4, then there would be values in four columns.

I want to learn the above formula. Can you please explain it?

Explaining every piece of the formula involved in the final complex formula, that returns the average of every n cells in Google Sheets, may not be practical. Because it will increase the length of this tutorial!

So if you are very keen to know/learn the above formula, please read the formula explanation here in a separate tutorial – Move Single Column to Multiple Columns Using Hlookup in Google Sheets.

MMULT to Average Every N Cells as an Array in Google Sheets – Step 2

Please take a close look at image # 2 above. As per the image we want to find the average of every 3 cells as the value in cell H2 is 3.

The formula above re-arrange the values in column E to columns I to K (1 column to 3 columns).

Non-Array Average

If we use the below AVERAGE formula in cell L2 and drag-down we will get the average of every 3 cells of column E, right?

=average(I2:K2)

We want an array formula to return the average of every n cells in Google Sheets. So we can’t use the AVERAGE function in our formula. Instead we can use MMULT. How?

Array Average

As you may know, the MMULT matrix multiplication function takes two arguments – matrix1 and matrix2.

Syntax: MMULT(matrix1, matrix2)

If we use the above HLOOKUP array formula (step # 1 formula) as matrix1 and the following SEQUENCE formula as matrix2 we would get the sum of every n cells.

ArrayFormula(SEQUENCE(H2,1)^0)

So as per my example, the formula to SUM every n cells in Google Sheets will be as below.

=ArrayFormula(mmult(N(IFERROR(hlookup("test",E1:E,SEQUENCE(roundup(COUNTA(E1:E)/H2),H2,2),0))),sequence(H2,1)^0))

As a side note, there is a non-array formula for the same. Here is the link to that post but not relevant here – How to Sum Every N Cells to the Right or Bottom in Google Sheets.

Just divide the above MMULT result by n (value in cell H2) to get the average of every n cells. Here it is!

=ArrayFormula(mmult(N(IFERROR(hlookup("test",E1:E,SEQUENCE(roundup(COUNTA(E1:E)/H2),H2,2),0))),sequence(H2,1)^0)/H2)

Step 2 - MMULT Formula to Average Every N Cells as a Range — image # 3

I am going to make some more changes to the above formula which won’t affect the result anyway. The aim is to remove any extra blank cells at the last part of the result array.

I’ll use the Array_Constrain function for that.

Step 2 Formula:

=ArrayFormula(ARRAY_CONSTRAIN(mmult(N(IFERROR(hlookup("test",E1:E,SEQUENCE(roundup(COUNTA(E1:E)/H2),H2,2),0))),sequence(H2,1)^0)/H2,count(sequence(mround(count(E2:E),H2)/H2,1)),1))

If you just want the sum or average of every n cells, you can stop reading here! But if you want to distribute the average to corresponding rows in column F as per image # 1, then please read further.

Sequence Numbers as Per Count of N Cells in Column E – Step 3

We are going to use Vlookup to distribute the average array from column J to column F as per n cells.

As the first step to this, we should insert sequence numbers against the average array returned by MMULT in J2:J (refer image # 3).

First of all, we should find how many rows are there in the MMULT result. Here is that cool formula.

=mround(count(E2:E),H2)/H2

Then, we can use the above MROUND output as the number of rows in Sequence.

Step 3 Formula:

=SEQUENCE(mround(count(E2:E),H2)/H2,1)

Explanation:

The MROUND has been used to round the count of the number of values in E2:E to the nearest integer multiple of the every n value in cell H2.

To get the number of rows in the MMULT result, divide the above-returned number by n. Then generate a sequence from 1 to that returned number. We are here now!

Step 3 - Sequence Numbers as Per Count of Rows in MMULT — image # 4

Sequence of Test Values and MOD – Step 4

We are progressing towards the array formula that returns the average of every n cells in Google Sheets. Just two steps are left including this.

Here once again we are going to use the above MROUND output in Sequence. But we won’t divide the output by n.

As per my sample data in column E, the below MROUND in Sequence will return the sequence numbers from 1 to 15.

=SEQUENCE(mround(count(E2:E),H2),1)

Now in cell L2 let’s use the above formula as the ‘dividend’ in MOD and use the ‘n’ as the ‘divisor’. So you will get a column with numbers and every nth cells in that column will have the value 0.

Step 4 Formula:

=ArrayFormula(mod(sequence(mround(count(E2:E),H2),1),H2))

Step 4 - Sequence of Test Values and MOD — image # 5

Distribute the Average to Every N Cells – Step 5

Now time to distribute the MMULT result which is the average of every n cells in the range E2:E.

Before that let’s insert the following running count formula in cell M2 which would return the running count of the value 0 in column L. Don’t forget to check column M on the image below.

Step 5 Formula:

=ARRAYFORMULA(if(indirect("L2:L"&mround(count(E2:E),H2)+1)=0,COUNTIFS(L2:L,L2:L,ROW(L2:L),"<="&ROW(L2:L)),))

The above running count output in column M acts as the search key in Vlookup in cell F2. The range in Vlookup is the MMULT result.

Formula to Return Average of Every N Cells in Google Sheets (Helper Columns Based):

=ArrayFormula(IFNA(vlookup(M2:M,I1:J,2,0)))

Step 5 - Distribute the Average Array to Every N Cells — image # 6

Vlookup will match the sequence numbers in column M in the first column of the ‘range’ (MMULT array) and will return the average values against the corresponding search_keys. This helps us to distribute the average values to every n cells.

Final Formula (Without Helper Columns)

We can combine all of the above formulas. I mean the Vlookup in cell F2, the sequence of MMULT in cell I2, the MMULT formula in cell J2 that returns the average array of every n cells, MOD of ‘n’ Sequence in cell L2, and the running count of Sequence MOD in cell M2.

Here is that final single array formula in cell F2 (see image # 1) that returns the average of every n cells in Google Sheets.

=ArrayFormula(IFNA(vlookup(if(mod(sequence(mround(count(E2:E),H2),1),H2)=0,COUNTIFS(mod(sequence(mround(count(E2:E),H2),1),H2),mod(sequence(mround(count(E2:E),H2),1),H2),sequence(mround(count(E2:E),H2),1),"<="&sequence(mround(count(E2:E),H2),1)),),{sequence(mround(count(E2:E),H2)/H2,1),array_constrain(mmult(N(IFERROR(hlookup("test",E1:E,SEQUENCE(roundup(COUNTA(E1:E)/H2),H2,2),0))),sequence(H2,1)^0)/H2,count(sequence(mround(count(E2:E),H2)/H2,1)),1)},2,0)))

Usage Note:

You may always keep a few extra rows (n extra rows) at the end of the values in column E.
Copy my sample sheet below to get the formula in action.
Make sure that the count of the number of values in column E is the multiples of ‘n.

example_sheet_23620

Related Resources: