We can use the AVERAGE function to calculate the running average in Google Sheets.

Since a running arithmetic mean/average requires repeated calculations, if we use the said function, we may require to use more than one formula.

But there are functions like SUMIF, MMULT, and DAVERAGE, that we can use to write a **running average array formula** in Google Sheets.

**Introduction**

An average (arithmetic mean) of a dataset is a single number that shows a central or normal value for it.

We can get this value by adding up all the numbers in the dataset and dividing it (the sum) by the count of all numbers in the dataset.

Assume the number of employees who have attended the past four weekly meetings in your office is 5, 4, 4, and 6.

To get the average number of employees who have attended the meeting, add the numbers 5, 4, 4, and 6 and divide the sum by 4.

Average = (5 + 6 + 6 + 7) / 4 = 24 / 4 = 6

We can calculate it using the below AVERAGE formula in Google Sheets.

`=average(5,6,6,7)`

When a new weekly meeting is held next week, this arithmetic mean is likely to change.

Imagine the number of employees who have attended is five this time.

Average = (5 + 6 + 6 + 7 + 5) / 5 = 29 / 5 = 5.8

`=average(5,6,6,7,5)`

With knowing or unknowing, you have involved in calculating the running average. Let’s see now how to implement the above in Sheets.

**How to Calculate Running Average in Google Sheets**

We can use two types of formulas for calculating the running average in Google Sheets.

The simplest one is a non-array formula.

As per the following sample dataset, the dates of weekly meetings are in column A, and the number of attendants is in column B.

To calculate the running average, we can use the below formula in cell C2 and copy-paste it down by dragging the fill handle as shown below.

`=average($B$2:B2)`

The formula will ignore any blank cell in the range B2:B8 but will count 0 (zero).

**Calculating Running Average Using Array Formulas in Google Sheets**

I have three array formula options and included all of them below.

In a small dataset, all of them will work exceptionally well. But the first formula seems a little better to handle large datasets.

It’s up to you which one to pick.

Note:- I have opened the range in all of the three formulas. Make it close, for example, B2:B to B2:B100, to improve their performance.

Here are the first two formulas and their explanations.

**First Two Array Formulas for Running Average and Their Explanations**

The formulas are based on SUMIF and MMULT. You may empty C2:C before inserting either of the ones in cell C2.

Formula 1 (SUMIF): **Recommended** as it seems performance-wise better.

`=ArrayFormula( if(B2:B="",, `**sumif(row(B2:B),"<="&row(B2:B),B2:B)/ifna(vlookup(row(B2:B),filter(row(B2:B),B2:B<>"")**, sequence(counta(B2:B),1),2,0)) )

)

I will explain it after the formula below.

Formula 2 (MMULT):

`=ArrayFormula( if(B2:B="",, `**MMULT(IF(ROW(B2:B)>=TRANSPOSE(ROW(B2:B))=TRUE,1,0),n(B2:B))/ifna(vlookup(row(B2:B),filter(row(B2:B),B2:B<>"")**, sequence(counta(B2:B),1),2,0)) )

)

**Formula Explanations**

There are two main parts in both the formulas.

Part_1:

In the above two running average array formulas in Google Sheets, the bold part (part_1) returns the cumulative sum of each datapoint in the range B2:B.

You can find the details of them under *Array Formula 1* and *Array Formula 2* in my following tutorial – Normal and Array-Based Running Total Formula in Google Sheets.

Part_2:

The second part, the sequence after the bold part_1, returns the numbers from 1 to ‘n’ and skips blank cells as detailed here – Skip Blank Rows in Sequential Numbering in Google Sheets.

While coding, I have followed the below syntax.

`part_1/part_2=running_average`

**Database Function (Array Formula 3)**

Formula 3 (DAVERAGE):

`=ArrayFormula( if(B2:B="",, DAVERAGE( transpose(B2:B,`**TRANSPOSE(if(ROW(B2:B) <= TRANSPOSE(ROW(B2:B)),B2:B,))**), sequence(rows(B2:B),1), if(,,);if(,,) ) )

)

Can you imagine using a database function for calculating cumulative (arithmetic) mean in Spreadsheets?

The above formula is an example of how versatile Google Sheets is.

Earlier, we have used database functions DMIN and DMAX to return running min and max in Google Sheets.

I have used one of those formulas here and replaced DMIN/DMAX with DAVERAGE.

Wait! I have also made one more change which is not mandatory, though.

What’s that?

When you go through the bold part in the above DAVERAGE formula for running average, you can find that it’s also a part of the MMULT above.

The bold part here returns 1-12-123-1234 patterns.

In the above-said two DMIN/DMAX tutorials, I have used a different formula to generate that pattern.

That’s all about how to return the running average in Google Sheets.

Thanks for the stay. Enjoy!

## No comments:

Write Comments