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

paulgunther

New Member
Joined
Oct 30, 2019
Messages
12
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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
Joined
Oct 30, 2019
Messages
12
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))

 

paulgunther

New Member
Joined
Oct 30, 2019
Messages
12

ADVERTISEMENT

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
Joined
Apr 4, 2009
Messages
3,680
Not familiar with Golf ...

Are you referring to the Callaway Handicap System ?
 

paulgunther

New Member
Joined
Oct 30, 2019
Messages
12

ADVERTISEMENT

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
Joined
Aug 18, 2015
Messages
10,807
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:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,807
Well, you know the saying, "Practice makes prefect!" :)

Glad we could help!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,475
Messages
5,636,549
Members
416,923
Latest member
jarri

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
Top