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 />

paulgunther

New Member
Hello again! I have another twist to this spreadsheet. As it turns out, we now have variable par for each round. (some days it's 54, some days it's 56 and some it's 57. The formula from above served the purpose using Par 54 for all rounds, but it is not as accurate. I still want to put the total strokes for the round and not the over/under for the day. I've put a row in for the changing Par value "Today's Par". All other conditions are the same as stated above. I hope this explains what I'm looking for. Here's a snapshot of the spreadsheet...
￿￿

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

paulgunther

New Member
 Today's Par 57 56 56 56 55 Rounds HCP Player 12/29/19 12/22/19 12/15/19 12/8/19 12/1/19 11/24/19 11/17/19 11/10/19 11/3/19 10/27/19 27 1 Player 1 52 53 47 55 53 50 3 -4 Player 2 13 1 Player 3 58 55 33 0 Player 4 56 57 52 53 50 52 59 22 -1 Player 5 55 55 56 56 51 51 52 52 33 -13 Player 6 78 68 3 -3 Player 7 55 61 8 -4 Player 8 58 60 57 57 58 59 6 -2 Player 9 5 -5 Player 10 8 -11 Player 11 3 -19 Player 12 17 -11 Player 13 65 63 66 64 66 8 0 Player 14 57 52 53 51 56 5 -5 Player 15 3 -2 Player 16 57 3 -15 Player 17 3 -7 Player 18 6 -12 Player 19 29 -3 Player 20 56 60 57 50 57 57 54 50 53 62

Eric W

MrExcel MVP
This actually ramps up the level of difficulty quite a bit. I haven't got an answer yet, but here's a clarification question. When you drop the high and low scores, do you want to drop the absolute high and low scores, or relative high and low scores based on the variable par?

paulgunther

New Member
This actually ramps up the level of difficulty quite a bit. I haven't got an answer yet, but here's a clarification question. When you drop the high and low scores, do you want to drop the absolute high and low scores, or relative high and low scores based on the variable par?
Great question Eric, It should be done after the variable par. I'm not against using another spreadsheet if needed to gracefully do the calculations. I just need the ability to visually look through the database and see scores, not over/under par. I could easily just enter the over/under scores for each day if I didn't have that need.

Eric W

MrExcel MVP

Try:

Book1
ABCDEFGHIJKLM
1Today's Par57565656555755555658
2RoundsHCPPlayer12/29/201912/22/201912/15/201912/8/201912/1/201911/24/201911/17/201911/10/201911/3/201910/27/2019
327-4Player 1525347555350
43#DIV/0!Player 2
513#NUM!Player 35855
633-3Player 456575253505259
722-1Player 55555565651515252
833#NUM!Player 67868
933Player 75561
1082Player 8586057575859
116#NUM!Player 9
125#NUM!Player 10
138#NUM!Player 11
143#DIV/0!Player 12
15178Player 136563666466
168-3Player 145752535156
175#NUM!Player 15
1831Player 1657
193#DIV/0!Player 17
203#DIV/0!Player 18
216#NUM!Player 19
22291Player 2056605750575754505362
Sheet1
Cell Formulas
RangeFormula
B3:B22B3{=IF(A3<3,"NoHCP",IF(A3<5,ROUND(AVERAGE(IF(D3:M3<>"",D3:M3-D\$1:M\$1)),0),ROUND(AVERAGE(SMALL(IF((D3:M3<>"")*(COLUMN(D3:M3)<=SMALL(IF(D3:M3<>"",COLUMN(D3:M3)),5)),D3:M3-D\$1:M\$1),{2,3,4})),0)))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

The errors in column B are due to there not being enough scores on those lines. I imagine your sheet goes further to the right. Also, I just used a basic ROUND function, something else may be called for. Anyway, take a look and see if this works for you.

paulgunther

New Member
Thanks Eric! That works perfectly.

paulgunther

New Member
Hey guys. I'm upgrading the Handicap spreadsheet again and need your help. We are now looking to only use the most current scores of the last six months. All other calculations remain the same.

As a reminder of what the objective of this spreadsheet is, it's to calculate handicaps for each player based on the last 5 rounds played dropping the highest and lowest scores leaving 3 scores to compute the average. The "Handicap" is the ultimate goal and is represented by the factor that their round will be adjusted. The Par value changes randomly and is noted in it's own row.

Here's a sample of the spreadsheet with working formulas.
Sunday CP Handicap League v2.xlsm
1
2Covid 19 Break
3Today's Par545454555558585858586060575757565656555555
4RoundsHCPPlayer5/31/205/24/205/17/205/10/205/3/204/26/204/19/204/12/204/5/203/29/203/22/203/15/203/8/203/1/202/23/202/16/202/9/202/2/201/26/201/19/201/12/201/5/2012/29/1912/22/1912/15/1912/8/1912/1/1911/24/1911/17/19
56-10Player 1617166697172
6304Player 2565451525347
73-3Player 3
83-46Player 48187139
9210Player 5545755576759625458
10382Player 65350535352565752535052
11262Player 7575167485555565651
1233-12Player 87868
133-2Player 955
147-7Player 1060606560665966
1510-2Player 11506758605757
166-1Player 12
175-4Player 13
1810-10Player 147068
193-23Player 15708179
204-17Player 1670
2122-4Player 175860596270656366
2213-2Player 185656585859575253
236-3Player 1961
Sample
Cell Formulas
RangeFormula
F4:AH4F4=G4+7
B5:B23B5=COUNTIF(E5:HR5,">0")
C5:C23C5=IF(B5<3,"NoHCP",(IF(B5<5,ROUND(AVERAGE(IF(E5:CW5<>"",E5:CW5-E\$3:CW\$3)),0),ROUND(AVERAGE(SMALL(IF((E5:CW5<>"")*(COLUMN(E5:CW5)<=SMALL(IF(E5:CW5<>"",COLUMN(E5:CW5)),5)),E5:CW5-E\$3:CW\$3),{2,3,4})),0)))*(-1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Eric W

MrExcel MVP
Boy, I wrote that formula and I can barely remember how it works!

So when you add a new week, do you just insert a new column before F? Is this just a matter of making sure the formulas just have a range of 26 weeks (columns)?

paulgunther

New Member
Boy, I wrote that formula and I can barely remember how it works!

So when you add a new week, do you just insert a new column before F? Is this just a matter of making sure the formulas just have a range of 26 weeks (columns)?
Yes you did Eric. And it's been rock solid since!

You're correct. The new column is added between E/F to keep the formula intact. And also correct that we now only want to include the last 26 columns. I'm sure it's just a matter of using a COUNT formula in place of the end of the arrays, but I just don't do enough of this to stuff to figure it out. It would be nice to do a hard 26 count that I can change if six months doesn't work.

Anyway, there you go. Hope you can figure this out and thanks so much!

Replies
2
Views
114
Replies
2
Views
263
Replies
1
Views
270
Replies
6
Views
450
Replies
4
Views
491

1,127,212
Messages
5,623,424
Members
415,974
Latest member
ZorroOP

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.

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

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