Excel Formula for shooting competition calculations

This calculates the average of the best 5 of the last 6 cards shot (as often required for competition entry)
and the difficult part is to find the range which includes the last 6 shot results which moves to the right
as more results are added.  Also account for byes in this range by 'expanding' the range if needed.

=(SUM(OFFSET(scores,0,rnds-6-byes,1,6+byes))-SMALL(OFFSET(scores,0,rnds-6-byes,1,6+byes),1)) /
       (COUNT(OFFSET(scores,0,rnds-6-byes,1,6+byes))-1)

named ranges:
scores 	the scores for that season - a row of 12 cells 
rnds	the number of rnds so far (incl ncr and byes)
byes	the number rounds for which a bye or ncr returned (used to 'expand' range to include 6 real results)

This calculates the average of the best 5 of ALL cards shot (much simpler to do)
=AVERAGE(LARGE(scores,1),LARGE(scores,2),LARGE(scores,3),LARGE(scores,4),LARGE(scores,5),LARGE(scores,6))

                                         
Andy Tate ~ updated March 2004 
contact me for more info if interested.