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,748
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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,748
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,210
Messages
5,623,410
Members
415,972
Latest member
SY1234

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