Week to week averages

americanpie3

Board Regular
Joined
Jul 16, 2003
Messages
176
Hello,

I run a dart league that runs for 30 weeks. I have a weekly worksheet that I enter the scores and the column Z (starting from 2 all the way down) calculates the average =IFERROR(AVERAGE($B2:$X2),0) and the player's name is on A2 of the sheet

Then I have my results worksheet that gets that info for that player =INDEX(Week1!$Z$1:$Z$60,MATCH(A2,Week1!$A$1:$A$60,0))

That works well. Now here is what I'd like to simplify.

Week 2 on column Z I have this formula =IFERROR(AVERAGE(Week1!B2:X2,Week2!B2:X2),0) so you can imagine what week 30 looks like. As you can see, the player has to always be on A2. So if that player is on like A15 that week, I have to change my formula of week 2 to reflect that R(AVERAGE(Week1!B2:X2,Week2!B15:X15),0).

So to eliminate manual labour, is there something else I can do? It can be on either my sheets of week1 or week2 and so on, or is it better to put it in my results page?

Thank you
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,941
What if you put your data on one sheet (instead of 30) and just used a new "Week Number" column to differentiate between weeks?

If it's all on one page, you could use something like:

Code:
=SUMPRODUCT((A2:A500="americanpie3")*B2:X500)/SUMPRODUCT((A2:A500="americanpie3")*(B2:X500>0))
 
Last edited:

americanpie3

Board Regular
Joined
Jul 16, 2003
Messages
176
Because we play multiple games per week. Here is what my weekly sheet looks like:

<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 /><col /><col /><col /><col /><col /><col /><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>D</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><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th><th>AH</th><th>AI</th><th>AJ</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Team 1</td><td style=";">Game 1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Game 2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Game 3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Game 4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Game 5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Game 6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Total</td><td style=";">Average</td><td style=";">High Score</td><td style=";">Finish</td><td style=";">High Finish</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">25 paid</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">J-C</td><td style="text-align: right;;">52</td><td style="text-align: right;;">26</td><td style="text-align: right;;">41</td><td style=";">J-C</td><td style="text-align: right;;">37</td><td style="text-align: right;;">14</td><td style="text-align: right;;">30</td><td style=";">J-C</td><td style="text-align: right;;">57</td><td style="text-align: right;;">81</td><td style="text-align: right;;">81</td><td style=";">J-C</td><td style="text-align: right;;">41</td><td style="text-align: right;;">78</td><td style="text-align: right;;">43</td><td style=";">J-C</td><td style="text-align: right;;">23</td><td style="text-align: right;;">93</td><td style="text-align: right;;">22</td><td style=";">J-C</td><td style="text-align: right;;">78</td><td style="text-align: right;;">44</td><td style="text-align: right;;">41</td><td style="text-align: right;;">882</td><td style="text-align: right;;">49.00</td><td style="text-align: right;;">93</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Jonathan</td><td style="text-align: right;;">40</td><td style="text-align: right;;">58</td><td style="text-align: right;;">2</td><td style=";">Jonathan</td><td style="text-align: right;;">24</td><td style="text-align: right;;">30</td><td style="text-align: right;;">3</td><td style=";">Jonathan</td><td style="text-align: right;;">13</td><td style="text-align: right;;">11</td><td style="text-align: right;;">45</td><td style=";">Jonathan</td><td style="text-align: right;;">52</td><td style="text-align: right;;">26</td><td style="text-align: right;;">52</td><td style=";">Jonathan</td><td style="text-align: right;;">58</td><td style="text-align: right;;">10</td><td style="text-align: right;;">45</td><td style=";">Jonathan</td><td style="text-align: right;;">6</td><td style="text-align: right;;">66</td><td style="text-align: right;;">40</td><td style="text-align: right;;">581</td><td style="text-align: right;;">32.28</td><td style="text-align: right;;">66</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Roland</td><td style="text-align: right;;">11</td><td style="text-align: right;;">92</td><td style="text-align: right;;">11</td><td style=";">Roland</td><td style="text-align: right;;">85</td><td style="text-align: right;;">25</td><td style="text-align: right;;">30</td><td style=";">Roland</td><td style="text-align: right;;">31</td><td style="text-align: right;;">37</td><td style="text-align: right;;">65</td><td style=";">Roland</td><td style="text-align: right;;">24</td><td style="text-align: right;;">56</td><td style="text-align: right;;">32</td><td style=";">Roland</td><td style="text-align: right;;">46</td><td style="text-align: right;;">22</td><td style="text-align: right;;">45</td><td style=";">Roland</td><td style="text-align: right;;">12</td><td style="text-align: right;;">36</td><td style="text-align: right;;">39</td><td style="text-align: right;;">699</td><td style="text-align: right;;">38.83</td><td style="text-align: right;;">92</td><td style="text-align: right;;">2</td><td style="text-align: right;;">40</td><td style="text-align: right;;">40</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">8</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Close</td><td style=";">Roland</td><td style=";">Score</td><td style="text-align: right;;">40</td><td style=";">Close</td><td style="text-align: right;;"></td><td style=";">Score</td><td style="text-align: right;;"></td><td style=";">Close</td><td style="text-align: right;;"></td><td style=";">Score</td><td style="text-align: right;;"></td><td style=";">Close</td><td style=";">Roland</td><td style=";">Score</td><td style="text-align: right;;">8</td><td style=";">Close</td><td style="text-align: right;;"></td><td style=";">Score</td><td style="text-align: right;;"></td><td style=";">Close</td><td style="text-align: right;;"></td><td style=";">Score</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Team 2</td><td style=";">Game 1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Game 2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Game 3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Game 4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Game 5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Game 6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Total</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Sylvain</td><td style="text-align: right;;">45</td><td style="text-align: right;;">44</td><td style="text-align: right;;">36</td><td style=";">Sylvain</td><td style="text-align: right;;">92</td><td style="text-align: right;;">29</td><td style="text-align: right;;">37</td><td style=";">Sylvain</td><td style="text-align: right;;">50</td><td style="text-align: right;;">18</td><td style="text-align: right;;">48</td><td style=";">Sylvain</td><td style="text-align: right;;">11</td><td style="text-align: right;;">85</td><td style="text-align: right;;">28</td><td style=";">Sylvain</td><td style="text-align: right;;">19</td><td style="text-align: right;;">22</td><td style="text-align: right;;">65</td><td style=";">Sylvain</td><td style="text-align: right;;">28</td><td style="text-align: right;;">64</td><td style="text-align: right;;">35</td><td style="text-align: right;;">756</td><td style="text-align: right;;">42.00</td><td style="text-align: right;;">92</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Marc-André</td><td style="text-align: right;;">19</td><td style="text-align: right;;">45</td><td style="text-align: right;;">29</td><td style=";">Marc-André</td><td style="text-align: right;;">25</td><td style="text-align: right;;">36</td><td style="text-align: right;;">13</td><td style=";">Marc-André</td><td style="text-align: right;;">58</td><td style="text-align: right;;">40</td><td style="text-align: right;;">6</td><td style=";">Marc-André</td><td style="text-align: right;;">14</td><td style="text-align: right;;">38</td><td style="text-align: right;;">37</td><td style=";">Marc-André</td><td style="text-align: right;;">43</td><td style="text-align: right;;">33</td><td style="text-align: right;;">26</td><td style=";">Marc-André</td><td style="text-align: right;;">60</td><td style="text-align: right;;">79</td><td style="text-align: right;;">107</td><td style="text-align: right;;">708</td><td style="text-align: right;;">39.33</td><td style="text-align: right;;">107</td><td style="text-align: right;;">3</td><td style="text-align: right;;">40</td><td style="text-align: right;;">14</td><td style="text-align: right;;">40</td><td style="text-align: right;;">0</td><td style="text-align: right;;">22</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Sylvie F</td><td style="text-align: right;;">25</td><td style="text-align: right;;">53</td><td style="text-align: right;;">30</td><td style=";">Sylvie F</td><td style="text-align: right;;">43</td><td style="text-align: right;;">26</td><td style="text-align: right;;">24</td><td style=";">Sylvie F</td><td style="text-align: right;;">40</td><td style="text-align: right;;">18</td><td style="text-align: right;;">100</td><td style=";">Sylvie F</td><td style="text-align: right;;">40</td><td style="text-align: right;;">53</td><td style="text-align: right;;">25</td><td style=";">Sylvie F</td><td style="text-align: right;;">11</td><td style="text-align: right;;">58</td><td style="text-align: right;;">50</td><td style=";">Sylvie F</td><td style="text-align: right;;">33</td><td style="text-align: right;;">60</td><td style="text-align: right;;">18</td><td style="text-align: right;;">707</td><td style="text-align: right;;">39.28</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:4em;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)">Week1</p><br /><br />

The averages are in column Z

Here is the results page
<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 /></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>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</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;;">Team 1</td><td style="font-weight: bold;text-align: center;;">Sex</td><td style="font-weight: bold;text-align: center;;">Season Avg</td><td style="font-weight: bold;text-align: center;;">Avg Last week</td><td style="font-weight: bold;text-align: center;;">High Score Season</td><td style="font-weight: bold;text-align: center;;">High Score Last Week</td><td style="font-weight: bold;text-align: center;;">Finishes Season</td><td style="font-weight: bold;text-align: center;;">Finishes Last Week</td><td style="font-weight: bold;text-align: center;;">Highest Finish Season</td><td style="font-weight: bold;text-align: center;;">Highest Finish Last Week</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;;">J-C</td><td style="text-align: center;;">M</td><td style="text-align: center;;">49.00</td><td style="text-align: center;;">49.00</td><td style="text-align: center;;">93</td><td style="text-align: center;;">93</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;;">Jonathan</td><td style="text-align: center;;">M</td><td style="text-align: center;;">32.28</td><td style="text-align: center;;">32.28</td><td style="text-align: center;;">66</td><td style="text-align: center;;">66</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;;">Roland</td><td style="text-align: center;;">M</td><td style="text-align: center;;">38.83</td><td style="text-align: center;;">38.83</td><td style="text-align: center;;">92</td><td style="text-align: center;;">92</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">40</td><td style="text-align: center;;">40</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</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;">6</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;">7</td><td style="font-weight: bold;text-align: center;;">Team 2</td><td style="font-weight: bold;text-align: center;;">Sex</td><td style="font-weight: bold;text-align: center;;">Season Avg</td><td style="font-weight: bold;text-align: center;;">Avg Last week</td><td style="font-weight: bold;text-align: center;;">High Score Season</td><td style="font-weight: bold;text-align: center;;">High Score Last Week</td><td style="font-weight: bold;text-align: center;;">Finishes Season</td><td style="font-weight: bold;text-align: center;;">Finishes Last Week</td><td style="font-weight: bold;text-align: center;;">Highest Finish Season</td><td style="font-weight: bold;text-align: center;;">Highest Finish Last Week</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: center;;">Sylvain</td><td style="text-align: center;;">M</td><td style="text-align: center;;">42.00</td><td style="text-align: center;;">42.00</td><td style="text-align: center;;">92</td><td style="text-align: center;;">92</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: center;;">Marc-André</td><td style="text-align: center;;">M</td><td style="text-align: center;;">39.33</td><td style="text-align: center;;">39.33</td><td style="text-align: center;;">107</td><td style="text-align: center;;">107</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">40</td><td style="text-align: center;;">40</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: center;;">Sylvie F</td><td style="text-align: center;;">F</td><td style="text-align: center;;">39.28</td><td style="text-align: center;;">39.28</td><td style="text-align: center;;">100</td><td style="text-align: center;;">100</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr></tbody></table><p style="width:11.2em;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)">SeasonSummary1</p><br /><br />

So in C2 the formula is =INDEX(Week1!$Z$1:$Z$60,MATCH(A2,Week1!$A$1:$A$60,0))

So as you can see, I cannot put it all on one page
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,941
Try this:

Somewhere in your workbook (could be a new sheet, could be on your summary sheet... doesn't really matter), list the names of the worksheets you want to pull from, presumably Week1, Week2, ..., Week30.

Select the range of cells with those sheet names, click in the Name Box (just above column A's header), and type SheetNames to assign the named range SheetNames to reference that list. Note that the list should only include sheets you already have (i.e. if you only have tabs for Week1, Week2, and Week3, those should be the only sheets listed there... if you need to, you can adjust the named range in formulas --> name manager --> edit].

Then, use something like:

Code:
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!A1:A100"),A2,INDIRECT("'"&SheetNames&"'!Z1:Z100")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetNames&"'!A1:A100"),A2))
That formula sums the averages from column Z for the person listed in cell A2 then divides by the number of times the person listed in cell A2 was found on each of the weekly tabs. That inherently assumes you always play 6 games each week, but it wouldn't matter if, for example, J-C was in row 2 on one sheet and row 25 on another.
 

Forum statistics

Threads
1,081,914
Messages
5,362,052
Members
400,668
Latest member
seacubs17

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top