How to average the last 5 nonblank, nonzero value in a row in Google Sheets?

Question

I run a disc golf league and in order to track handicaps we take an average of the last 5 rounds played. I currently have the player name in column A, the handicap in B, # of rounds played in C, best round in D, then a manual average of the last 5 in E. then the next 33 columns are the weekly scores for each player.

I'm wondering if there is a formula that can capture the last 5 (or less) weeks scores and average them, accounting for the fact that not every player plays every week, so there are lots of blank cells.

Answer 1

Most Recent to Oldest

If the date of the weeks in the range F:AM decrease as the column number increases then these formulas will work

Example:

F	G	H	I
Week 33	Week 32	Week 31	Week 30

Single Row Formula

• FILTER removes any empty cells from columns F-AM (33 columns)
• ARRAY_CONSTRAIN limits the array to a maximum of 5 columns
• AVERAGE gets the average of the values in the array

=AVERAGE(
  ARRAY_CONSTRAIN(
    FILTER(F1:AM1, F1:AM1<>""),
    1, 5))

BYROW Formula

• BYROW maps each row in the range F2:AM100, one-by-one, into a LAMBDA function
• LAMBDA function applies a formula against each set of values it is passed, using the arbitrarily named variable r to represent the mapped values in the formula
• the Single Row Formula (above) is adapted simply by removing the range that was specified and replacing it with the named variable r

=BYROW(F1:AM100, LAMBDA(r, 
   AVERAGE(
    ARRAY_CONSTRAIN(
      FILTER(r, r<>""),
        1,5))))

 

Oldest to Most Recent

If the date of the weeks in the range F:AM increase as the column number increases then these formulas will work.

Example:

F	G	H	I
Week 1	Week 2	Week 3	Week 4

Single Row (Reverse Order) Formula

I created a LAMBDA version to make the formula easier to read, but in the code example showed the non-LAMBDA version as well)

• FILTER removes any empty cells from columns F-AM (33 columns)
• LAMBDA uses an arbitrarily named variable r to represent the FILTER function to make it easier to be reused multiple times in the formula
• COUNTA gives us the number of values in r
• SEQUENCE creates a column of numbers starting at 1, incrementing by 1, and increasing until it reaches the number of values in r
• SORTN sorts r by using the reverse order of the SEQUENCE column and returning a maximum of 5 values
• AVERAGE is then applied to the array

=LAMBDA(r, AVERAGE(
    SORTN(TRANSPOSE(r),5,,SEQUENCE(COUNTA(r)),FALSE)))
 (FILTER(F1:AM1, F1:AM1<>""))


# Non-LAMBDA Version

=AVERAGE(
   SORTN(TRANSPOSE(FILTER(F1:AM1, F1:AM1<>"")), 5,,
         SEQUENCE(COUNTA(FILTER(F1:AM1, F1:AM1<>""))),
         FALSE))

BYROW Formula (Reverse order)

• BYROW maps each row in the range F2:AM100, one-by-one, into a LAMBDA function
• LAMBDA function applies a formula against each set of values it is passed, using the arbitrarily named variable s to represent the mapped values in the formula
• the Single Row (Reverse Order) Formula (above) is adapted simply by removing the range that was specified and replacing it with the named variable s

=BYROW(F1:AM100, LAMBDA(s, LAMBDA(r, AVERAGE(
    SORTN(TRANSPOSE(r),5,,SEQUENCE(COUNTA(r)),FALSE)))
 (FILTER(s, s<>""))))


# Adapting with the non-LAMBDA version
# of the Single Row (Reverse Order) 
# Formula instead

=BYROW(F1:AM100, LAMBDA(s, AVERAGE(
    SORTN(TRANSPOSE(FILTER(s, s<>"")), 5,,
          SEQUENCE(COUNTA(FILTER(s, s<>""))),FALSE))