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

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.

• Is the most recent week in `F` or is `F` the oldest week? Commented Mar 14, 2023 at 3:03
• F is the most recent week Commented Mar 14, 2023 at 4:52

# 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)
• answer updated. FYI the advantage of the `BYROW` versions is that you define the range once and the single formula will generate the average for all your rows. Commented Mar 14, 2023 at 5:31