Golf Score Spreadsheet

D

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.
 
M

Mark Graesser

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
[email protected]
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
 
D

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
Click to expand...
(B3:L3) said:
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
Click to expand...
last score and the last score, selects that range, sums
up whats in it, and divides by 4.
 
Top