Golf Score Spreadsheet

[Donald Bruns]

Rows are players, columns are weekly scores, but players
don't always play every week. I need a function that
will calculate the average score for the last 4 times
each player played regardless of when they played.

 

[Guest]

Hi Donald
It's not the prettiest formula I've ever put together, put it works

=SUMPRODUCT(--(OFFSET(A3,0,LARGE((B3:L3<>"")*COLUMN(B3:L3),4)-1,1,LARGE((B3:L3<>"")*COLUMN(B3:L3),1)-LARGE((B3:L3<>"")*COLUMN(B3:L3),4)+1)))/

This would give the average for the player on row 3
A3 - the first cell in the row, used to offset from
B3:L3 - range of cells containing scores, adjust to your range

Basically it finds the column numbers for the 3rd from last score and the last score, selects that range, sums up whats in it, and divides by 4

Let me know if you have any trouble with it

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Donald Bruns wrote: ----

Rows are players, columns are weekly scores, but players
don't always play every week. I need a function that
will calculate the average score for the last 4 times
each player played regardless of when they played

 

[Donald Bruns]

Thanks much, I will give it a try and get back.
Don

-----Original Message-----
Hi Donald,
It's not the prettiest formula I've ever put together, put it works.

=SUMPRODUCT(--(OFFSET(A3,0,LARGE((B3:L3<>"")*COLUMN

This would give the average for the player on row 3.
A3 - the first cell in the row, used to offset from.
B3:L3 - range of cells containing scores, adjust to your range.

Basically it finds the column numbers for the 3rd from

last score and the last score, selects that range, sums
up whats in it, and divides by 4.