Calculate AVERAGE from LOWEST 8 values from a maximum range of 20 events

Hello Gurus,

Working on some golf handicapping calculations. Yes, this is a topic that's popped up on the Forums in the past and I've read through lots of threads which have been of great assistance; but running into situation where I'm trying to calculate the handicap (average of the 8 lowest scores from the most recent 20 games played) while ignoring blank cells from selected range(s).

My calculations are currently working, up to the point until I have my 9th or more rounds played, and then I'm no longer getting an average of my LOWEST 8 rounds but merely a average of most recent 8 scores.

Sample calculation currently used:

=IF(AN4>0,AVERAGE(INDEX(X4:AN4, LARGE(IF(X4:AN4<>"", MATCH(COLUMN(X4:AN4),COLUMN(X4:AN4)), ""), BQ4)):INDEX(X4:AN4,MATCH(1E+307, X4:AN4))),"")

AX:AN are differentials
BQ is a helper field referring to number of events that should be used in calculations
CD is a helper field (in attachment) that can be used to determine maximum range of scores or offsets

I've attached a truncated spreadsheet, plus additional notes and a complete manual breakdown of what scores should be getting detected but aren't and a cross-comparison of manual handicap calculations vs. what I'm actually getting. There are a couple of helper cells included and if additional helper cells are required that's OK too, but would like to keep them on the same ROW as each ROW will represent an individual participant. The attached example only consists of a single participant.

Many thanks in advance for anyone able to take a closer look. Thanks!!

Please try at C4

=IF(AF4="","",AVERAGE(SMALL($AF4:AF4,ROW($A$1:INDEX($A:$A,MIN(8,COUNT($AF4:AF4)))))))

or

=IF(AF4="","",AVERAGE(SMALL($AF4:AF4,SEQUENCE(MIN(8,COUNT($AF4:AF4))))))

May be

Please Login or Register  to view this content.

But there is only 20 items in source
last 5,8 6,1 6,5 4,8 8,5 6,5 5,8 that was in the manual calculation i cant find.

Please try at C4

=IF(AM4="","",AVERAGE(SMALL(INDEX($AM4:AM4,MAX(1,COUNT($AM4:AM4)-19)):AM4,ROW($A$1:INDEX($A:$A,MIN(8,COUNT($AM4:AM4)))))))