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!

Book1
	A	B	C	E	F	G	H	I	J	K	L	M	N	O	P
1	Rounds
2	Played	HCP		10/27/19	10/20/19	10/13/19	10/6/19	9/29/19	9/22/19	9/15/19	9/8/19	9/1/19	8/25/19	8/18/19	8/11/19
3	6	-5	Player 1	66		58		60		56		55		58
4	6	-5	Player 2		63					61	55	58		60	61
5	6	-3	Player 3			54		63		59	54		57		55
6	8	-5	Player 4		54	61		62		61	57	56	51		67
7	8	2	Player 5	52	53			56		52	47	54	58	50
8	9	-3	Player 6	60		54	57	56		58	57	57	64		57
9	10	2	Player 7	51	51		54	53		51	52	53	47	56	55
Scores

 

[Eric W]

No doubt there's a better way, but this was quick:

Book4
	A	B	C	D	E	F	G	H
1
2	31
3				Today's Par		54	54	54
4		Rounds	HCP	Player		5/31/2020	5/24/2020	5/17/2020
5		6	-10	Player 1
6		6	4	Player 2
7		0	NoHCP	Player 3
8		3	-46	Player 4				81
9		9	0	Player 5
10		11	2	Player 6		53	50	53
11		9	2	Player 7
12		2	NoHCP	Player 8
13		1	NoHCP	Player 9
14		7	-7	Player 10		60	60
15		6	#NUM!	Player 11		50
16		0	NoHCP	Player 12
17		0	NoHCP	Player 13
18		2	NoHCP	Player 14				70
19		3	-23	Player 15		70	81	79
20		1	NoHCP	Player 16
21		8	-4	Player 17				58
22		8	-2	Player 18		56	56	58
23		1	NoHCP	Player 19
Sheet6

Cell Formulas
Range	Formula
F4:H4	F4	=G4+7
B5	B5	=COUNTIF(INDEX(5:5,6):INDEX(5:5,$A$2),">0")
C5:C23	C5	=IF(B5<3,"NoHCP",(IF(B5<5,ROUND(AVERAGE(IF(INDEX(5:5,6):INDEX(5:5,$A$2)<>"",INDEX(5:5,6):INDEX(5:5,$A$2)-INDEX($3:$3,6):INDEX($3:$3,$A$2))),0),ROUND(AVERAGE(SMALL(IF((INDEX(5:5,6):INDEX(5:5,$A$2)<>"")*(COLUMN(INDEX(5:5,6):INDEX(5:5,$A$2))<=SMALL(IF(INDEX(5:5,6):INDEX(5:5,$A$2)<>"",COLUMN(INDEX(5:5,6):INDEX(5:5,$A$2))),5)),INDEX(5:5,6):INDEX(5:5,$A$2)-INDEX($3:$3,6):INDEX($3:$3,$A$2)),{2,3,4})),0)))*(-1))
B6:B23	B6	=COUNTIF(E6:HR6,">0")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

It just replaces E5:CW5 with INDEX(5:5,6):INDEX(5:5,$A$2) and E$3:CW$3 with INDEX($3:$3,6):INDEX($3:$3,$A$2) everywhere in the formulas, where 6 is the starting column and $A$2 (31) is the ending column (31-6+1 = 26 weeks).

If you define 2 Names as shown, you can shorten the formula a lot, as well as making it more readable.

Book4
	A	B	C	D	E	F	G	H
1
2	31
3				Today's Par		54	54	54
4		Rounds	HCP	Player		5/31/2020	5/24/2020	5/17/2020
5		6	-10	Player 1
6		6	4	Player 2
7		0	NoHCP	Player 3
8		3	-46	Player 4				81
9		9	0	Player 5
10		11	2	Player 6		53	50	53
11		9	2	Player 7
12		2	NoHCP	Player 8
13		1	NoHCP	Player 9
14		7	-7	Player 10		60	60
15		6	#NUM!	Player 11		50
16		0	NoHCP	Player 12
17		0	NoHCP	Player 13
18		2	NoHCP	Player 14				70
19		3	-23	Player 15		70	81	79
20		1	NoHCP	Player 16
21		8	-4	Player 17				58
22		8	-2	Player 18		56	56	58
23		1	NoHCP	Player 19
Sheet6

Cell Formulas
Range	Formula
F4:H4	F4	=G4+7
B5	B5	=COUNTIF(RowRange,">0")
C5:C23	C5	=IF(B5<3,"NoHCP",(IF(B5<5,ROUND(AVERAGE(IF(RowRange<>"",RowRange-TopRow)),0),ROUND(AVERAGE(SMALL(IF((RowRange<>"")*(COLUMN(RowRange)<=SMALL(IF(RowRange<>"",COLUMN(RowRange)),5)),RowRange-TopRow),{2,3,4})),0)))*(-1))
B6:B23	B6	=COUNTIF(E6:HR6,">0")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Named Ranges
Name	Refers To	Cells
RowRange	=INDEX(Sheet6!$1:$999,ROW(),6):INDEX(Sheet6!$1:$999,ROW(),Sheet6!$A$2)	B5, C5:C23
TopRow	=INDEX(Sheet6!$1:$999,3,6):INDEX(Sheet6!$1:$999,3,Sheet6!$A$2)	B5, C5:C23

Sheet6 was where I tested, change yours to match your workbook. You define Names on the Formula tab.

 

[paulgunther]

So if I insert a column each week at F, will this redefine the Named Ranges making them larger than the 26 weeks I'm looking for?

 

[Eric W]

No, the column numbers are fixed, not dynamic. They will always stay 6-31. If we used a range of F:Z, and you added a column before F, it would redefine the range. But not when I hardcoded the columns as numbers, not letters.