Need to calculate Average of first 5 non-zero cells in a ROW

[paulgunther]

In this example, each score is entered for only the players that played this week leaving lots of blank cells. A new column is added each week between Column D(Hidden) and E. I did this so the arrays in formulas will automatically update when a column is inserted inside the array.

In Column B, I need to calculate a handicap based on only the last 5 rounds played. The “Handicap” is the difference between Par (54) and the average.
EXAMPLE: Last 5 rounds are 50,46,49,50,60 The Average = 51 and the Handicap = +3 (54-Average)
I’ve tried to do an augmented version of Excel Magic Trick 677 to no avail. Thanks in advance!

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">Rounds</td><td style="text-align: center;;"></td><td style="text-align: center;font-style: italic;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">Played</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">HCP</td><td style="font-weight: bold;text-align: right;color: #FFFFFF;background-color: #305496;;"></td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">10/27/19</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">10/20/19</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">10/13/19</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">10/6/19</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">9/29/19</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">9/22/19</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">9/15/19</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">9/8/19</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">9/1/19</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">8/25/19</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">8/18/19</td><td style="font-weight: bold;text-align: center;color: #FFFFFF;background-color: #305496;;">8/11/19</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;;">6</td><td style="text-align: center;;">-5</td><td style=";">Player 1</td><td style="text-align: center;;">66</td><td style="text-align: center;;"></td><td style="text-align: center;;">58</td><td style="text-align: center;;"></td><td style="text-align: center;;">60</td><td style="text-align: center;;"></td><td style="text-align: center;;">56</td><td style="text-align: center;;"></td><td style="text-align: center;;">55</td><td style="text-align: center;;"></td><td style="text-align: center;;">58</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;;">6</td><td style="text-align: center;;">-5</td><td style=";">Player 2</td><td style="text-align: center;;"></td><td style="text-align: center;;">63</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">61</td><td style="text-align: center;;">55</td><td style="text-align: center;;">58</td><td style="text-align: center;;"></td><td style="text-align: center;;">60</td><td style="text-align: center;;">61</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">-3</td><td style=";">Player 3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">54</td><td style="text-align: center;;"></td><td style="text-align: center;;">63</td><td style="text-align: center;;"></td><td style="text-align: center;;">59</td><td style="text-align: center;;">54</td><td style="text-align: center;;"></td><td style="text-align: center;;">57</td><td style="text-align: center;;"></td><td style="text-align: center;;">55</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: center;;">8</td><td style="text-align: center;;">-5</td><td style=";">Player 4</td><td style="text-align: center;;"></td><td style="text-align: center;;">54</td><td style="text-align: center;;">61</td><td style="text-align: center;;"></td><td style="text-align: center;;">62</td><td style="text-align: center;;"></td><td style="text-align: center;;">61</td><td style="text-align: center;;">57</td><td style="text-align: center;;">56</td><td style="text-align: center;;">51</td><td style="text-align: center;;"></td><td style="text-align: center;;">67</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;">2</td><td style=";">Player 5</td><td style="text-align: center;;">52</td><td style="text-align: center;;">53</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">56</td><td style="text-align: center;;"></td><td style="text-align: center;;">52</td><td style="text-align: center;;">47</td><td style="text-align: center;;">54</td><td style="text-align: center;;">58</td><td style="text-align: center;;">50</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: center;;">9</td><td style="text-align: center;;">-3</td><td style=";">Player 6</td><td style="text-align: center;;">60</td><td style="text-align: center;;"></td><td style="text-align: center;;">54</td><td style="text-align: center;;">57</td><td style="text-align: center;;">56</td><td style="text-align: center;;"></td><td style="text-align: center;;">58</td><td style="text-align: center;;">57</td><td style="text-align: center;;">57</td><td style="text-align: center;;">64</td><td style="text-align: center;;"></td><td style="text-align: center;;">57</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: center;;">10</td><td style="text-align: center;;">2</td><td style=";">Player 7</td><td style="text-align: center;;">51</td><td style="text-align: center;;">51</td><td style="text-align: center;;"></td><td style="text-align: center;;">54</td><td style="text-align: center;;">53</td><td style="text-align: center;;"></td><td style="text-align: center;;">51</td><td style="text-align: center;;">52</td><td style="text-align: center;;">53</td><td style="text-align: center;;">47</td><td style="text-align: center;;">56</td><td style="text-align: center;;">55</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Scores</p><br /><br />

 

[paulgunther]

Hello again! I have another twist to this spreadsheet. As it turns out, we now have variable par for each round. (some days it's 54, some days it's 56 and some it's 57. The formula from above served the purpose using Par 54 for all rounds, but it is not as accurate. I still want to put the total strokes for the round and not the over/under for the day. I've put a row in for the changing Par value "Today's Par". All other conditions are the same as stated above. I hope this explains what I'm looking for. Here's a snapshot of the spreadsheet...
￿￿

 

[paulgunther]

		Today's Par	57	56	56	56	55
Rounds	HCP	Player	12/29/19	12/22/19	12/15/19	12/8/19	12/1/19	11/24/19	11/17/19	11/10/19	11/3/19	10/27/19
27	1	Player 1	52	53			47			55	53	50
3	-4	Player 2
13	1	Player 3	58								55
33	0	Player 4	56	57	52	53	50		52		59
22	-1	Player 5	55	55			56	56	51	51	52	52
33	-13	Player 6	78				68
3	-3	Player 7							55	61
8	-4	Player 8	58		60		57		57	58	59
6	-2	Player 9
5	-5	Player 10
8	-11	Player 11
3	-19	Player 12
17	-11	Player 13	65	63	66						64	66
8	0	Player 14	57					52	53	51		56
5	-5	Player 15
3	-2	Player 16			57
3	-15	Player 17
3	-7	Player 18
6	-12	Player 19
29	-3	Player 20	56	60	57	50	57	57	54	50	53	62

 

[Eric W]

This actually ramps up the level of difficulty quite a bit. I haven't got an answer yet, but here's a clarification question. When you drop the high and low scores, do you want to drop the absolute high and low scores, or relative high and low scores based on the variable par?

 

[paulgunther]

This actually ramps up the level of difficulty quite a bit. I haven't got an answer yet, but here's a clarification question. When you drop the high and low scores, do you want to drop the absolute high and low scores, or relative high and low scores based on the variable par?

Great question Eric, It should be done after the variable par. I'm not against using another spreadsheet if needed to gracefully do the calculations. I just need the ability to visually look through the database and see scores, not over/under par. I could easily just enter the over/under scores for each day if I didn't have that need.

 

[Eric W]

ADVERTISEMENT

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			Today's Par	57	56	56	56	55	57	55	55	56	58
2	Rounds	HCP	Player	12/29/2019	12/22/2019	12/15/2019	12/8/2019	12/1/2019	11/24/2019	11/17/2019	11/10/2019	11/3/2019	10/27/2019
3	27	-4	Player 1	52	53			47			55	53	50
4	3	#DIV/0!	Player 2
5	13	#NUM!	Player 3	58								55
6	33	-3	Player 4	56	57	52	53	50		52		59
7	22	-1	Player 5	55	55			56	56	51	51	52	52
8	33	#NUM!	Player 6	78				68
9	3	3	Player 7							55	61
10	8	2	Player 8	58		60		57		57	58	59
11	6	#NUM!	Player 9
12	5	#NUM!	Player 10
13	8	#NUM!	Player 11
14	3	#DIV/0!	Player 12
15	17	8	Player 13	65	63	66						64	66
16	8	-3	Player 14	57					52	53	51		56
17	5	#NUM!	Player 15
18	3	1	Player 16			57
19	3	#DIV/0!	Player 17
20	3	#DIV/0!	Player 18
21	6	#NUM!	Player 19
22	29	1	Player 20	56	60	57	50	57	57	54	50	53	62
Sheet1

Cell Formulas
Range	Formula
B3:B22	B3	{=IF(A3<3,"NoHCP",IF(A3<5,ROUND(AVERAGE(IF(D3:M3<>"",D3:M3-D$1:M$1)),0),ROUND(AVERAGE(SMALL(IF((D3:M3<>"")*(COLUMN(D3:M3)<=SMALL(IF(D3:M3<>"",COLUMN(D3:M3)),5)),D3:M3-D$1:M$1),{2,3,4})),0)))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

The errors in column B are due to there not being enough scores on those lines. I imagine your sheet goes further to the right. Also, I just used a basic ROUND function, something else may be called for. Anyway, take a look and see if this works for you.

 

[paulgunther]

Thanks Eric! That works perfectly.

 

[Eric W]

ADVERTISEMENT

Glad it works for you!

 

[paulgunther]

Hey guys. I'm upgrading the Handicap spreadsheet again and need your help. We are now looking to only use the most current scores of the last six months. All other calculations remain the same.

As a reminder of what the objective of this spreadsheet is, it's to calculate handicaps for each player based on the last 5 rounds played dropping the highest and lowest scores leaving 3 scores to compute the average. The "Handicap" is the ultimate goal and is represented by the factor that their round will be adjusted. The Par value changes randomly and is noted in it's own row.

Here's a sample of the spreadsheet with working formulas.

Sunday CP Handicap League v2.xlsm
	A	B	C	D		F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1
2									Covid 19 Break
3				Today's Par		54	54	54									55	55	58	58	58	58	58	60	60	57	57	57	56	56	56	55	55	55
4		Rounds	HCP	Player		5/31/20	5/24/20	5/17/20	5/10/20	5/3/20	4/26/20	4/19/20	4/12/20	4/5/20	3/29/20	3/22/20	3/15/20	3/8/20	3/1/20	2/23/20	2/16/20	2/9/20	2/2/20	1/26/20	1/19/20	1/12/20	1/5/20	12/29/19	12/22/19	12/15/19	12/8/19	12/1/19	11/24/19	11/17/19
5		6	-10	Player 1													61		71				66		69	71	72
6		30	4	Player 2																56						54	51	52	53			47
7		3	-3	Player 3
8		3	-46	Player 4				81									87								139
9		21	0	Player 5													54	57	55	57		67	59		62		54	58
10		38	2	Player 6		53	50	53										53								52		56	57	52	53	50		52
11		26	2	Player 7														57					51		67		48	55	55			56	56	51
12		33	-12	Player 8																								78				68
13		3	-2	Player 9																														55
14		7	-7	Player 10		60	60											65		60		66	59		66
15		10	-2	Player 11		50													67									58		60		57		57
16		6	-1	Player 12
17		5	-4	Player 13
18		10	-10	Player 14				70																		68
19		3	-23	Player 15		70	81	79
20		4	-17	Player 16																			70
21		22	-4	Player 17				58										60	59				62		70			65	63	66
22		13	-2	Player 18		56	56	58															58				59	57					52	53
23		6	-3	Player 19															61
Sample

Cell Formulas
Range	Formula
F4:AH4	F4	=G4+7
B5:B23	B5	=COUNTIF(E5:HR5,">0")
C5:C23	C5	=IF(B5<3,"NoHCP",(IF(B5<5,ROUND(AVERAGE(IF(E5:CW5<>"",E5:CW5-E$3:CW$3)),0),ROUND(AVERAGE(SMALL(IF((E5:CW5<>"")*(COLUMN(E5:CW5)<=SMALL(IF(E5:CW5<>"",COLUMN(E5:CW5)),5)),E5:CW5-E$3:CW$3),{2,3,4})),0)))*(-1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

Boy, I wrote that formula and I can barely remember how it works!

So when you add a new week, do you just insert a new column before F? Is this just a matter of making sure the formulas just have a range of 26 weeks (columns)?

 

[paulgunther]

Boy, I wrote that formula and I can barely remember how it works!

So when you add a new week, do you just insert a new column before F? Is this just a matter of making sure the formulas just have a range of 26 weeks (columns)?

Yes you did Eric. And it's been rock solid since!

You're correct. The new column is added between E/F to keep the formula intact. And also correct that we now only want to include the last 26 columns. I'm sure it's just a matter of using a COUNT formula in place of the end of the arrays, but I just don't do enough of this to stuff to figure it out. It would be nice to do a hard 26 count that I can change if six months doesn't work.

Anyway, there you go. Hope you can figure this out and thanks so much!