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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,746
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.
 

Some videos you may like

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
Joined
Oct 30, 2019
Messages
12
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
Joined
Aug 18, 2015
Messages
10,746
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.
 

Watch MrExcel Video

Forum statistics

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