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]

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:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB

<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]41[/TD]

[TD="align: right"]41[/TD]
[TD="align: right"]35[/TD]

[TD="align: right"]35[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]41[/TD]

[TD="align: right"]42[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]41[/TD]

[TD="align: right"]42[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]37.2[/TD]
[TD="align: right"]38.1[/TD]
[TD="align: right"]37.9[/TD]
[TD="align: right"]37.1[/TD]
[TD="align: right"]36.8[/TD]
[TD="align: right"]36.8[/TD]
[TD="align: right"]37.4[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas
[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]U3[/TH]
[TD="align: left"]=AVERAGE(SMALL(A2:T2,{1,2,3,4,5,6,7,8,9,10}))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Enter the formula in U3 and drag it right as needed.

 

[cmancu]

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

 

[Eric W]

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?

 

[cmancu]

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.

 

[Eric W]

How about this then:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB

<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>
[TD="align: center"]2[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]41[/TD]

[TD="align: right"]41[/TD]
[TD="align: right"]35[/TD]

[TD="align: right"]35[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]41[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]41[/TD]

[TD="align: right"]42[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"][/TD]
[TD="align: right"]36[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]35.8[/TD]
[TD="align: right"]36.5[/TD]
[TD="align: right"]36.5[/TD]
[TD="align: right"]36.3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]T3[/TH]
[TD="align: left"]{=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}))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




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.

 

[Eric W]

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.

 

[cmancu]

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

 

[Eric W]

Happy to help!

Enjoy your golfing!

 

[cmancu]

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