We all know that Google Sheets is a powerful tool for organizing and analyzing data. One useful calculation that you may need to perform is calculating the running average or, as some may call it, the moving average (MA) in Google Sheets.
But before we dive into the details, let’s clarify the difference between running average and simple moving average (SMA). The running average is a constantly updated average value, while the SMA is the average of a given set of values over a specific number of days in the past.
Now, let’s explore how we can calculate the running average in Google Sheets using different formulas and techniques.
Non-Array Formula
The simplest method to calculate the running average in Google Sheets is by using a non-array formula. This method involves adding the AVERAGE formula and adjusting the range as we go.
For example, assuming we have a dataset of weekly meetings attendance, where the number of attendees is listed in column B, we can use the following formula in cell C2 to calculate the running average:
=average($B$2:B2)
By dragging the fill handle down, the formula will automatically adjust to include the previous values, providing the running average for each row.
Please note that this formula will ignore any blank cells in the range B2:B8 but will count 0 (zero) as a value.
Running (Moving) Average Using Array Formulas
Aside from the non-array formula, we can also utilize array formulas to calculate the running average in Google Sheets. Array formulas allow us to perform calculations on multiple cells in a range simultaneously.
In the following example, we have four running average array formula examples in Google Sheets. Although all of them work well for small datasets, the first and fourth formulas are better suited for handling larger datasets.
Here are the formulas explained:
SUMIF – MA Array Formula 1
=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))))MMULT – MA Array Formula 2
=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))))DAVERAGE – MA Array Formula 3
=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(,,)})))BYROW LHF – MA Array Formula 4 (New!)
=byrow(B2:B,lambda(r,if(r="",,average(filter(B2:B,row(B2:B)<=row(r)))))) Each formula has its own advantages, so it’s up to you to choose the one that suits your needs.
It’s important to note that the formulas mentioned above have certain syntax and structure. They consist of two main parts: part_1 and part_2. Part_1 calculates the cumulative sum of each data point in the range B2:B, while part_2 returns a sequence of numbers and skips blank cells.
For a detailed explanation of how these formulas work, you can refer to the original article.
Conclusion
Calculating the running average in Google Sheets can be a valuable tool for analyzing data trends. Whether you choose to use a non-array formula or array formulas, Google Sheets offers various methods to calculate this important statistic.
To learn more about different types of averages and formulas in Google Sheets, feel free to visit Crawlan.com for more informative articles.
