+ Reply to Thread
Results 1 to 5 of 5

Average of previous 6 entries with 2 criteria using date.

  1. #1
    Registered User
    Join Date
    01-12-2013
    Location
    ayr
    MS-Off Ver
    Excel 2007
    Posts
    4

    Angry Average of previous 6 entries with 2 criteria using date.

    Hi folks,

    I'm looking for a formula that extracts the average goals of the previous 6 games for a given team, ie using the date of team A's 7th game I'm looking for the average goals of team A's games 1 to 6 inclusive.

    Can't get it to work, I hope someone can help as it would save me hours of huffing and puffing.

    TEAM DATE GOALS
    Team A 1st Jan 2012 3
    Team B 1st Jan 2012 2
    Team C 2nd Jan 2012 0
    Team A 8th Jan 2012 1

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Average of previous 6 entries with 2 criteria using date.

    does the formula need to find the "previous 6 games" based on date, or will they be listed chronilogically, meaning you just need to find the six rows where Team A is listed.
    Please click the * icon below if I have helped.

  3. #3
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Average of previous 6 entries with 2 criteria using date.

    The attached should work in both scenarios.
    Column A is a ranking based on team and Date, and the running average column finds an average of all the of the appropriate games (ex. In row 30, the average is for all entries that are Team B, and the A value is 9, 8, 7, 6, 5 and 4.
    Attached Files Attached Files

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Average of previous 6 entries with 2 criteria using date.

    Assuming that TEAM is from A1, GOALS from C1, D column is for running checking for any TEAM (not only TEAM A) for 6 games of the team in the past,
    In D1:
    Please Login or Register  to view this content.
    Confirmed with holding both Ctrl-shift, then press enter.
    Drag down.
    Attached Files Attached Files
    Quang PT

  5. #5
    Registered User
    Join Date
    01-12-2013
    Location
    ayr
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Average of previous 6 entries with 2 criteria using date.

    Thanks, both examples work fine and will save me loads of time.

+ 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