Golf league handicapping conundrum

cmancu

Board Regular
Joined
Jan 25, 2006
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
This is hard to comprehend exactly what you want, especially since you seem to be mixing up rows and columns. The cell references you cite don't jibe with your explanation. However, look at this and see if it's close:

ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
2323841413535384142453637394142363338444333
337.238.137.937.136.836.837.4

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
U3=AVERAGE(SMALL(A2:T2,{1,2,3,4,5,6,7,8,9,10}))

<tbody>
</tbody>

<tbody>
</tbody>


Enter the formula in U3 and drag it right as needed.
 
Upvote 0
So sorry - feel free to call me an idiot. Was up all night working on this. Yes of course, as you assumed, I meant to say ROW 1 for scores, ROW 2 for values, and ROW 3 for ave of ROW 2. However, I also was unclear in my asking. I want to look at only the 20 most recent NON-NULL cell values in row 2 to average the minimum 10 of these 20. That essentially is where I am stuck.

Your formula is almost it, but can it always look at only the last 20 non-null cells. Very grateful for your decoding my gibberish.

Chris
 
Upvote 0
I think my formula does what you want. First, the SMALL function ignores non-numeric values, so it ignores the null cells. Second, as you drag the formula to the right, the range changes from A2:T2 to B2:U2 to C2:V2 etc., meaning it only looks at the 20 most recent columns.

There is a possible issue. If there are more than 10 null cells in the 20 column range, you'll get a #NUM error. If that's an issue, tell me what you'd like to do: average what's there, leave the error?
 
Upvote 0
Yes, thank you, I noticed your formula ignores the non-numeric values. That's a help. But I need to look at the 20 most recent non-null columns, not just the 20 most recent columns for the range. That's where I'm stuck - the ole dynamic range. The look back range must include only exactly 20 of the most recent numeric values across the row. Then ave the lowest 10 of them. Helper cells are OK with me.
I tried all versions of formulas returning non-null cell values, but I kept getting stuck on the range problem. It needs to count 20 of those most recent numeric values.
Thanks so much for your patience.
 
Upvote 0
How about this then:

ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
2323841 4135 353841 50424536373941 42363338444336
3#NUM!#NUM!#NUM!#NUM!#NUM!35.836.536.536.3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
T3{=AVERAGE(SMALL(INDEX($A2:T2
,LARGE(IF(ISNUMBER($A2:T2),COLUMN($A2:T2)),20)
):T2,{1,2,3,4,5,6,7,8,9,10}
)
)}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>




This formula looks for non-null cells, and gets the column number of the 20th non-null cell from the right. If there are not 20 non-null cells, the formula returns an error. Then it creates a range using that cell as the starting point, and the current column cell as the end, and again extracts the 10 smallest values from that range and averages them.
 
Upvote 0
I noodled around with a couple of other ways, here's one:

T3: =AVERAGE(SMALL(INDEX($A2:T2,AGGREGATE(14,6,ISNUMBER($A2:T2)*COLUMN($A2:T2),20)):T2,{1,2,3,4,5,6,7,8,9,10}))

This doesn't require the Control+Shift+Enter, and it doesn't return an error. If there are not 20 scores yet, it returns the average of the 10 lowest so far.
 
Upvote 0
Thank you, thank you, thank you, good sir Eric. I see that the first works beautifully, I will play around later with the second option also, once I get back to the sheet. I trust it works just fine too, no doubt. Plus, I like the no error returns. Success, and without anyone wryly suggesting I go and buy myself some golf league software. HA!

(it's more fun doing myself and practicing formula creation, and learning from the best)

Best regards,
Chris
 
Upvote 0
Sorry to revisit this - thought you had it solved. But I am getting a quirky result from your formula, now that it is in practice.

I have J267:AF267 as follows:

23.5 | 23.5 | " " | 20.8 | 22.6 | 18.9 | " " | 14.3 | 17.1 | 18.9 | " " | 15.3 | 23.5 | 13.4 | 22.6 | 16.2 | 18 | 22.6 | 20.8 | 23.5 | 19.8 | 22.6 | 14.3

All of these values are results of the same formula returning either a value to tenths, or " " weekly. Columns will accrue.

In AF275 I put your formula =AVERAGE(SMALL(INDEX($J267:AF267,AGGREGATE(14,6,ISNUMBER($J267:AF267)*COLUMN($J267:AF267),20)):AF267,{1,2,3,4,5,6,7,8,9,10}))

It is returning 18.19. Yet when I copy data to a new sheet and try again, it returns 16.62, the correct result. When I copy it all again onto same sheet, it returns 18.19.

For review, the intent of the formula is to look back to the 20 most recent NON-EMPTY cells, and return the average of the 10 lowest values of those 20. I can't seem to produce 18.19 in any way manually, so I don't know where to look. Thought it may have to do with formula results, but no, does the same with just values entered across.

Any ideas are greatly appreciated!

Chris
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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
Back
Top