Excel golf handicap formula

[Golfnut1]

Right now my spreadsheet has a calculated handicap base on
the last 5 entries. I would like to add a new column that
will give the old handicap before the latest entry. The
weekly input of scores are in columns B thru W. How can I
do an average of the 5 scores previous to the last entry?
It has to be able to do the calculations when someone
misses a week. Thank you.

 

[Frank Kabel]

Hi
for the first row try the following array formula (entered
with CTRL+SHIFT+ENTER):
=AVERAGE(INDIRECT("R" & ROW() & "C" & LARGE(IF(ISNUMBER
(B1:S1),COLUMN(B1:W1)),6) & ":R" & ROW() & "C" & MATCH
(9.99999999999999E+307,B1:W1),FALSE))

and copy this down

 

[Aladin Akyurek]

Let B2:W2 be the first row of target data.


=AVERAGE(INDEX(A2:W2,LOOKUP(9.99999999999999E+307,1/LARGE((B2:W2>0)*COLUMN(B
2:W2),{1,2,3,4,5}),LARGE((B2:W2>0)*COLUMN(B2:W2),{1,2,3,4,5}))):W2)

which you need to confirm with control+shift+enter (not just with enter)
and copy down.

If you have the morefunc.xll add-in (http://longre.free.fr/english), the
foregoing formula can be shortened and speeded up:

=AVERAGE(INDEX(A2:W2,LOOKUP(9.99999999999999E+307,1/SETV(LARGE((B2:W2>0)*COL
UMN(B2:W2),{1,2,3,4,5})),GETV())):W2)