+ Reply to Thread
Results 1 to 11 of 11

Calculating a rolling average

  1. #1
    Registered User
    Join Date
    07-22-2008
    Location
    auburn
    Posts
    74

    Calculating a rolling average

    Alright, not sure if there's an easy solution to this one... I've really been struggling with it. What I want is a semi-easy way to calculate a team's average opponent points for and against. So for the 2008 spreadsheet I've attached I'd like a way to calculate how good the teams were on average that each team faced (a strength of schedule factor). I have included on the spreadsheet all of the relevant information... I just don't know how to go about grabbing the up to date opponent's information for every team a team has faced and then average it out.

    I'm struggling to even put this into words so let me know if you need clarification on anything. Any help is greatly appreciated... thanks!
    Attached Files Attached Files
    Last edited by roasthawg; 10-10-2009 at 12:05 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: NFL Spreadsheet

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    I'm sure it would be clearer if you didn't use the abbreviation NFL, but gave the full wording
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    07-22-2008
    Location
    auburn
    Posts
    74

    Re: Calculating a rolling average

    Bump for help... might be there's no easy solution.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculating a rolling average

    Can you outline your desired results ? Calc a couple manually - this way we can validate the logic - as is I'm not quite sure.

  5. #5
    Registered User
    Join Date
    07-22-2008
    Location
    auburn
    Posts
    74

    Re: Calculating a rolling average

    Ok, I've attached the updated spreadsheet to this post. In it I've manually done the calculation that I'm looking for for Kansas City in two different weeks... it should give you a good idea of what I'm trying to do. Thanks a lot!
    Attached Files Attached Files

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculating a rolling average

    I have to say I think you're going to want/need VBA based solution here (User Defined Function) - it is my opinion that a native solution will be hideously complex...

    If I'm correct I think you're saying you want for each fixture, for the visitors (and I guess latterly the home side) to

    a) determine all opponents faced up to and excluding present opponent

    b) determine the "last" avg. score of each of the opponents as outlined in a)

    c) the appropriate avg. score column will be determined by whether the opponent last played home or away

    are all of the above assumptions correct ?

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculating a rolling average

    On the off chance I've interpreted correctly see if the attached does what you want ?

    Uploaded file is 2007 format given

    a) use of 2007 specific function IFERROR
    b) compression

    The UDF here has been setup such that you're passing a multitude of ranges so as to keep setup dynamic - if you wanted to hardwire and simplify you could do.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-22-2008
    Location
    auburn
    Posts
    74

    Re: Calculating a rolling average

    Quote Originally Posted by DonkeyOte View Post
    On the off chance I've interpreted correctly see if the attached does what you want ?

    Uploaded file is 2007 format given

    a) use of 2007 specific function IFERROR
    b) compression

    The UDF here has been setup such that you're passing a multitude of ranges so as to keep setup dynamic - if you wanted to hardwire and simplify you could do.
    This looks awesome! Thank you so much for your help... I only just now glanced at it but it looks like from your above post that you understand what I am trying to do. I'm going to review this now and see if I can figure out how you did this... thank you again for your help!

  9. #9
    Registered User
    Join Date
    07-22-2008
    Location
    auburn
    Posts
    74

    Re: Calculating a rolling average

    Ok, after looking at it this is obviously much, much better than what I had. A slight adjustment and it would be perfect... I want it to give me every team's most current strength of schedule going into that week. This does that if the team is listed ABOVE all of it's opponents for that weeks games. But if one or more of the opponents are listed above the team that we're trying to calculate sos for then it uses the opponents prior averages- the averages do not include the games from the previous week. This is particularly troublesome in the early season games... in week 2 it will return a bunch of zero averages rather than the actual opponent averages.

    Anyways, thank you again for your help... either way this will help me a lot and I learned some more excel tricks while trying to figure out what you did. If you know a way to fix this one inconsistency I'd be glad to hear... thanks again!

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Calculating a rolling average

    Sorry for delay, I've not been on line much today.

    See if the attached revision works for you - I've not been able to test the logic personally as I've not the time available unfortunately.

    Basically the new code bases the week on the Monday Night Football date (whether there's a Monday night game or not) ie a week is classed as Tue-Mon (so Thurs pm games roll into following w/e "group"))
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-22-2008
    Location
    auburn
    Posts
    74

    Re: Calculating a rolling average

    Looks like it works great! Very ingenius way to solve the problem by using the weekly cycle!! Thank you sooooo much for your help on this... never in a million years could I have arrived at this solution as I thought it was beyond excel's capabilities to handle... turns out it was only beyond mine. If I find any issues with this after I mess around with it a little more I'll bother you again but it looks to be exactly what I was hoping for... thanks again for all your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1