I have a table in Google Sheets with data for different people by month. I want to display a monthly average that will be progressive as the year goes by. So they can have an idea of how they are doing. So I currently have the data divided by 12, but that is, of course, the average if there are no more occurrences for the rest of the year which isn't very helpful. I know I can do this manually as the year goes on by changing the denominator to the number the current month but was wondering if there is a way to have it happen automatically.
Google Sheets Formulas – Creating a Monthly Rolling Average
formulasgoogle sheetsgoogle-sheets-arrayformula
Related Topic
- Google Sheets – Get Average Function for Accumulative Input
- Google-sheets – How to write AverageIf (Not an Empty String) in Google Sheets
- Google-sheets – How to sum all positive values in Google Sheets based on whether the associated data in another column is unique
- Google-sheets – Blank Redundant Month
- Google-sheets – Compare dates from two years in a graph when dates not identical in Google Sheets
- Google-sheets – Google Sheets show running total and average on a chart
- Google-sheets – Add year (multiple different years) in cells that have only day and month (Google Sheets)
- Google-sheets – Sheets Chart with 5 data ranges – plots not lining up with X axis
Best Answer
Welcome to Web Apps SE. Thanks for your straightforward explanation and example sheet. I hope you get it working the way you need.
Just as you described, we need to divide by the number of entries, rather than always 12. The formula to do this in cell O3, which you could then drag down the rows, might resemble:
=IFERROR(N3/COUNTA(B3:M3))In English, that adds up the number of non-blanks using COUNTA, and divides by that count. When there are no entries, we'd be dividing by zero, so the IFERROR just hides the… "average of zero numbers" error.