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

#### Eric W

##### MrExcel MVP
No doubt there's a better way, but this was quick:

Book4
ABCDEFGH
1
231
3Today's Par545454
4RoundsHCPPlayer5/31/20205/24/20205/17/2020
56-10Player 1
664Player 2
70NoHCPPlayer 3
83-46Player 481
990Player 5
10112Player 6535053
1192Player 7
122NoHCPPlayer 8
131NoHCPPlayer 9
147-7Player 106060
156#NUM!Player 1150
160NoHCPPlayer 12
170NoHCPPlayer 13
182NoHCPPlayer 1470
193-23Player 15708179
201NoHCPPlayer 16
218-4Player 1758
228-2Player 18565658
231NoHCPPlayer 19
Sheet6
Cell Formulas
RangeFormula
F4:H4F4=G4+7
B5B5=COUNTIF(INDEX(5:5,6):INDEX(5:5,\$A\$2),">0")
C5:C23C5=IF(B5<3,"NoHCP",(IF(B5<5,ROUND(AVERAGE(IF(INDEX(5:5,6):INDEX(5:5,\$A\$2)<>"",INDEX(5:5,6):INDEX(5:5,\$A\$2)-INDEX(\$3:\$3,6):INDEX(\$3:\$3,\$A\$2))),0),ROUND(AVERAGE(SMALL(IF((INDEX(5:5,6):INDEX(5:5,\$A\$2)<>"")*(COLUMN(INDEX(5:5,6):INDEX(5:5,\$A\$2))<=SMALL(IF(INDEX(5:5,6):INDEX(5:5,\$A\$2)<>"",COLUMN(INDEX(5:5,6):INDEX(5:5,\$A\$2))),5)),INDEX(5:5,6):INDEX(5:5,\$A\$2)-INDEX(\$3:\$3,6):INDEX(\$3:\$3,\$A\$2)),{2,3,4})),0)))*(-1))
B6:B23B6=COUNTIF(E6:HR6,">0")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

It just replaces E5:CW5 with INDEX(5:5,6):INDEX(5:5,\$A\$2) and E\$3:CW\$3 with INDEX(\$3:\$3,6):INDEX(\$3:\$3,\$A\$2) everywhere in the formulas, where 6 is the starting column and \$A\$2 (31) is the ending column (31-6+1 = 26 weeks).

If you define 2 Names as shown, you can shorten the formula a lot, as well as making it more readable.

Book4
ABCDEFGH
1
231
3Today's Par545454
4RoundsHCPPlayer5/31/20205/24/20205/17/2020
56-10Player 1
664Player 2
70NoHCPPlayer 3
83-46Player 481
990Player 5
10112Player 6535053
1192Player 7
122NoHCPPlayer 8
131NoHCPPlayer 9
147-7Player 106060
156#NUM!Player 1150
160NoHCPPlayer 12
170NoHCPPlayer 13
182NoHCPPlayer 1470
193-23Player 15708179
201NoHCPPlayer 16
218-4Player 1758
228-2Player 18565658
231NoHCPPlayer 19
Sheet6
Cell Formulas
RangeFormula
F4:H4F4=G4+7
B5B5=COUNTIF(RowRange,">0")
C5:C23C5=IF(B5<3,"NoHCP",(IF(B5<5,ROUND(AVERAGE(IF(RowRange<>"",RowRange-TopRow)),0),ROUND(AVERAGE(SMALL(IF((RowRange<>"")*(COLUMN(RowRange)<=SMALL(IF(RowRange<>"",COLUMN(RowRange)),5)),RowRange-TopRow),{2,3,4})),0)))*(-1))
B6:B23B6=COUNTIF(E6:HR6,">0")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
Named Ranges
NameRefers ToCells
RowRange=INDEX(Sheet6!\$1:\$999,ROW(),6):INDEX(Sheet6!\$1:\$999,ROW(),Sheet6!\$A\$2)B5, C5:C23
TopRow=INDEX(Sheet6!\$1:\$999,3,6):INDEX(Sheet6!\$1:\$999,3,Sheet6!\$A\$2)B5, C5:C23

Sheet6 was where I tested, change yours to match your workbook. You define Names on the Formula tab.

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### paulgunther

##### New Member
So if I insert a column each week at F, will this redefine the Named Ranges making them larger than the 26 weeks I'm looking for?

#### Eric W

##### MrExcel MVP
No, the column numbers are fixed, not dynamic. They will always stay 6-31. If we used a range of F:Z, and you added a column before F, it would redefine the range. But not when I hardcoded the columns as numbers, not letters.

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

1,127,185
Messages
5,623,260
Members
415,958
Latest member
pandagurl

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

### Which adblocker are you using?

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