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

paulgunther

New Member
Joined
Oct 30, 2019
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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
ABCEFGHIJKLMNOP
1Rounds
2PlayedHCP10/27/1910/20/1910/13/1910/6/199/29/199/22/199/15/199/8/199/1/198/25/198/18/198/11/19
36-5Player 1665860565558
46-5Player 2636155586061
56-3Player 3546359545755
68-5Player 45461626157565167
782Player 55253565247545850
89-3Player 6605457565857576457
9102Player 751515453515253475655
Scores
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
 
Upvote 0
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))

 
Upvote 0
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.
 
Upvote 0
Not familiar with Golf ...

Are you referring to the Callaway Handicap System ?
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
Solution

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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