0

new to all this. Doing a golf spreadsheet and need to find out the average of lowest 8 rounds from the last 20 rounds. As I add in another scorecard, the calculation will move along etc.

Screenshot of spreadsheet

Any ideas on how I can get started? Thanks in advance

Jonty
  • 45
  • 1
  • 7
  • 1
    please share an actual example sheet rather than just an image... – MattKing Jul 10 '20 at 20:10
  • What are the last 20 rounds in your picture and what means lowest 8 rounds, the lowest sum, the lowest average, the lowest 8 single values? Can you make a clearer example. – OverflowStacker Jul 11 '20 at 13:24

2 Answers2

2

As you also tagged excel-formula, based on this formula for bottom n values, guessing that you want the average of the lowest column sums of most recent number of columns, a solution to your this guess would be

=AVERAGE(SMALL(OFFSET(A4:E4,,COUNTA(A4:J4)-5),{1,2,3}))

enter image description here

This formula gets the average of the lowest 3 column-sums(yellow row) of 5 most recent rounds (5 rightmost rows). You need to adapt the ranges.

For your case i can only guess, where your data starts.

=AVERAGE(SMALL(OFFSET(A23:T23,,COUNTA(A23:ZZ23)-20),{1,2,3,4,5,6,7,8}))

A23:T23 has to start at the first round (column). COUNTA(A23:ZZ23)-20 hast to start at first round (column) and has to be at least as long as the number of expected rounds. It counts the number of not-empty cells to get the last round and subtracts 20 to get the start of the last 20 rounds(columns)

I suppose, there should be a similar functionality in google-sheets, but I'm not familiar with it.

OverflowStacker
  • 1,340
  • 1
  • 10
  • 17
  • 1
    For Google sheets, "all" you have to do is put the SMALL function inside an ARRAYFORMULA function. The second example will become: ```=AVERAGE(ARRAYFORMULA(SMALL(OFFSET(A23:T23,,COUNTA(A23:ZZ23)-20),{1,2,3,4,5,6,7,8})))``` – Johan G Aug 25 '20 at 13:59
0

I have solved this issue in a vertical table. In column D is the Score differential for each round (columns A-C are the date, golf course and the holes). The function then becomes:

=AVERAGE(arrayformula(SMALL(OFFSET(D1:D$27,,,20),{1,2,3,4,5,6,7,8})))

Row 27 is the first entry, every new entry goes above line 1. I then copy the formula to the new cell E1 (D$27 will change to D$28 if you add a line above line 1)

OFFSET(D1:D$27,,,20) returns the first 20 rows of the range D1:D$27 ARRAYFORMULA(SMALL(..., {1,2,3,4,5,6,7,8})) Creates an array of 8 elements. The elements are SMALL(...,1), SMALL(...,2) ... SMALL(...,8). And the AVERAGE function takes the avareage of those 8 values. (SMALL(...,n) returns the nth smallest values of the provided range)

In a separate spreadsheet, I calculate the score differential based on the CR, SR, PAR, SI and handicap table.

Johan G
  • 407
  • 5
  • 12