# Google Sheets monthly rolling average

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.

• pls share a copy of your sheet or provide some example sheet with desired result – user0 Apr 11 at 16:48

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.

• delete column N, O and row 14
• paste in N2 cell:

``````={"Total", "Monthly average"; ARRAYFORMULA(IF(LEN(A3:A),
{MMULT(IF(B3:M<>"", B3:M, 0), TRANSPOSE(COLUMN(B3:M3))^0),
MMULT(IF(B3:M<>"", B3:M, 0), TRANSPOSE(COLUMN(B3:M3))^0)/COUNTA(B3:M3)}, ))}``````

• paste in A14 cell:

``````={"Total", ARRAYFORMULA(IF(LEN(INDIRECT("B2:"&ADDRESS(2, MONTH(TODAY())+1, 4))),
TRANSPOSE(MMULT(TRANSPOSE(IF(INDIRECT("B3:M"&ROW()-1)<>"",
INDIRECT("B3:M"&ROW()-1), 0)), ROW(INDIRECT("B3:M"&ROW()-1))^0)), ))}``````