How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Thursday

Calculating Running Average in Google Sheets (Array Formula)

Posted by   on

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)

Cumulative Arithmetic Mean in Google Sheets

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.


Running Average Array Formulas in Google Sheets

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!


Sample_Sheet_11221


Related Resources

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!