# Golf league handicapping conundrum

#### cmancu

##### New Member
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

##### MrExcel MVP
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}))

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### cmancu

##### New Member
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

Replies
4
Views
727
Replies
5
Views
625

1,147,731
Messages
5,742,852
Members
423,758
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back