Golf league handicapping conundrum

cmancu

New Member
Joined
Jan 25, 2006
Messages
37
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
Joined
Aug 18, 2015
Messages
11,666
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
Joined
Jan 25, 2006
Messages
37
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
 

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top