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

#### paulgunther

##### New Member
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 />

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### James006

##### Well-known Member
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

##### New Member
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))

You are welcome

#### paulgunther

##### New Member

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

##### Well-known Member
Not familiar with Golf ...

Are you referring to the Callaway Handicap System ?

#### paulgunther

##### New Member

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

##### MrExcel MVP

=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:

#### paulgunther

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

#### Eric W

##### MrExcel MVP
Well, you know the saying, "Practice makes prefect!"

Glad we could help!

Replies
2
Views
153
Replies
2
Views
269
Replies
1
Views
280
Replies
6
Views
461
Replies
4
Views
502

1,129,845
Messages
5,638,682
Members
417,042
Latest member
kshipp91

### 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?

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