1

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.

spreadsheet link

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

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.

0
  • 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)}, ))}

    0

  • 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)), ))}

    0

  • demo spreadsheet

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.