1

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.

2
  • Is the most recent week in F or is F the oldest week?
    – Blindspots
    Mar 14, 2023 at 3:03
  • 1
    F is the most recent week
    – jrhwhite
    Mar 14, 2023 at 4:52

1 Answer 1

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))
2
  • 1
    Thank you! That first formula (Single Row) is really close to what I am looking for, however I need it to average the last 5 values in the row. Is there a way to modify it to accomplish that?
    – jrhwhite
    Mar 14, 2023 at 4:52
  • 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.
    – Blindspots
    Mar 14, 2023 at 5:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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