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

 

[James006]

Hi,

In cell B3, you can test following array formula

54-AVERAGE(E3:INDEX(E3:Z3,SMALL(IF(ISNUMBER(E3:Z3),COLUMN(E3:Z3)-COLUMN(E3)+1),MIN(5,COUNT(E3:Z3)))))

All credit goes to daddylonglegs ... :wink:

Hope this will help

 

[paulgunther]

This worked great! Thanks.
I edited the formula to include the full data set, round offto whole numbers, include hidden column D so inserting a new Column at Eautomatically updates the formula and check to see if they completed at least 3rounds. It even accurately calculates aHandicap for 3 & 4 rounds. Here’sthe final product:
=IF(A4<3,"NoHCP",54-ROUND(AVERAGE(D4:INDEX(D4:BQ4,SMALL(IF(ISNUMBER(D4:BQ4),COLUMN(D4:BQ4)-COLUMN(D4)+1),MIN(5,COUNT(D4:BQ4))))),0))

 

[James006]

You are welcome

 

[paulgunther]

Bonus question: We may want to drop the highest and lowest scores of the last 5 scores to calculate the handicap of the 3 remaining scores. Also, If they only have 3 or 4 scores to date, don’t drop the high/low.

 

[James006]

Not familiar with Golf ...

Are you referring to the Callaway Handicap System ?

 

[paulgunther]

Not exactly. That system is way more complex than we need. This is Disc Golf, but the basic scoring is the same. Our league plays a Handicap round where your actual score is adjusted by the result of your eloquent formula(the Handicap). This levels the playing field between novices and pros so you’re competing solely on how much you’ve improved. Unlike ball golf where once your handicap reaches zero, you become a “Scratch” golfer and your score does not adjust.

EXAMPLE: Player 1 shoots a 59 with a -3 handicap (net 56) and Player 2 shoots a 49 with a 8 handicap (net 57), Player 1 wins even though Player 2 shot 10 strokes better.
The logic is Player 1 normally shoots 57 and Player 2 normally shoots 46, so Player 1 shot 2 strokes worse and Player 2 shot 3 strokes worse. Player 1 wins.

TMI for sure, but there it is.

As for dropping High/Low rounds, this eliminates the “fluke”rounds good and bad.

 

[Eric W]

How about this for the bonus question:

=IF(A4<3,"NoHCP",54-ROUND(IF(COUNT(D4:P4)<5,AVERAGE(D4:P4),AVERAGE(SMALL(D4:INDEX(D4:P4,MATCH(5,COUNTIF(OFFSET(D4,0,0,1,COLUMN(D4:P4)-COLUMN(D4)+1),">0"),0)),{2,3,4}))),0))

with Control+Shift+Enter.

 

[paulgunther]

Winner, winner! You guys are way better than me at this!

 

[Eric W]

Well, you know the saying, "Practice makes prefect!"

Glad we could help!