Results 1 to 4 of 4

Thread: Week to week averages
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2003
    Posts
    176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Week to week averages

    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

  2. #2
    MrExcel MVP Oaktree's Avatar
    Join Date
    Jun 2002
    Location
    Chicago, IL
    Posts
    7,931
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Week to week averages

    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 by Oaktree; Sep 17th, 2019 at 11:30 AM.
    "The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell

  3. #3
    Board Regular
    Join Date
    Jul 2003
    Posts
    176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Week to week averages

    Because we play multiple games per week. Here is what my weekly sheet looks like:

    ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
    1Team 1Game 1Game 2Game 3Game 4Game 5Game 6TotalAverageHigh ScoreFinishHigh Finish25 paid
    2J-C522641J-C371430J-C578181J-C417843J-C239322J-C78444188249.0093000000000
    3Jonathan40582Jonathan24303Jonathan131145Jonathan522652Jonathan581045Jonathan6664058132.2866000000004
    4Roland119211Roland852530Roland313765Roland245632Roland462245Roland12363969938.839224040008000
    5CloseRolandScore40CloseScoreCloseScoreCloseRolandScore8CloseScoreCloseScore
    6Team 2Game 1Game 2Game 3Game 4Game 5Game 6Total
    7Sylvain454436Sylvain922937Sylvain501848Sylvain118528Sylvain192265Sylvain28643575642.0092000000000
    8Marc-André194529Marc-André253613Marc-André58406Marc-André143837Marc-André433326Marc-André607910770839.331073401440022001
    9Sylvie F255330Sylvie F432624Sylvie F4018100Sylvie F405325Sylvie F115850Sylvie F33601870739.28100000000000

    Week1





    The averages are in column Z

    Here is the results page
    ABCDEFGHIJ
    1Team 1SexSeason AvgAvg Last weekHigh Score SeasonHigh Score Last WeekFinishes SeasonFinishes Last WeekHighest Finish SeasonHighest Finish Last Week
    2J-CM49.0049.0093930000
    3JonathanM32.2832.2866660000
    4RolandM38.8338.839292224040
    5
    6
    7Team 2SexSeason AvgAvg Last weekHigh Score SeasonHigh Score Last WeekFinishes SeasonFinishes Last WeekHighest Finish SeasonHighest Finish Last Week
    8SylvainM42.0042.0092920000
    9Marc-AndréM39.3339.33107107334040
    10Sylvie FF39.2839.281001000000

    SeasonSummary1





    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

  4. #4
    MrExcel MVP Oaktree's Avatar
    Join Date
    Jun 2002
    Location
    Chicago, IL
    Posts
    7,931
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Week to week averages

    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.
    "The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •