Golf league handicapping conundrum

[cmancu]

I built a golf handicapping sheet for my league of 30 players, each player has a row for score entries for 100+ rounds. Each week I enter into row A Frank's score, from A1 across. If Frank doesn't play that week, the entry is "0". Row B contains formulas in each cell from B1 across that look at A and either return "" for a "0", or a value under 50 to tenths, i.e. 1.7, 18.0, 0.6, -2.5, 10.4, 7.0, 22.8, 18.1, etc., depending on score in A. From C20 across, I need to find the ave of the minimum 10 values in B, but always from only the most recent 20 values in B.

I have from MrExcel help last year:

AVERAGE(SMALL($A1:AC1,1),SMALL($A1:AC1,2),SMALL($A1:AC1,3),SMALL($A1:AC1,4),SMALL($A1:AC1,5),SMALL($A1:AC1,6),SMALL($A1:AC1,7),SMALL($A1:AC1,8),SMALL($A1:AC1,9),SMALL($A1:AC1,10)

But this doesn't look at only the most recent 20 values from which to pull the 10 minimums to ave.

I greatly appreciate any thoughts.

Chris

 

[Eric W]

I expected that your range would start in column A. Since it doesn't, you need to adapt the formula as follows:

=AVERAGE(SMALL(INDEX($J267:AF267,AGGREGATE(14,6,ISNUMBER($J267:AF267)*(COLUMN($J267:AF267)-COLUMN($J267)+1),20)):AF267,{1,2,3,4,5,6,7,8,9,10}))

 

[cmancu]

Wonderful! I just got back and read this, tried it quickly in a few of my players rows, and of course, works perfectly!

All the best to you!

Chris