+ Reply to Thread
Results 1 to 9 of 9

Averageif, but only average last X # of values

  1. #1
    Registered User
    Join Date
    09-30-2012
    Location
    Madison, WI
    MS-Off Ver
    Excel 2010
    Posts
    48

    Averageif, but only average last X # of values

    Hi all. For this question I have two spreadsheets involved: Results and Dashboard. "Results" is a spreadsheet logging statistics over a certain time period. Column B contains the value I'm matching against, and AK is the column I'm averaging. Column B contains many different repeating values. Each day I copy and paste onto "Dashboard" in column A, the subset of values that I'm interested in on that day. I have an Averageif function next to that averaging any value from Results column AK if the value matches column B. I'm finding that it would be helpful to also have the averages of the last 10 entries for that particular value since they're in chronological order. The entries won't fall within a specific date range though, so the only criteria will be the fact that they're the last 10 entries. I may want to use the same formula again for a smaller set of entries like 3 or 5. Any help would be greatly appreciated. Thanks in advance!

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Averageif, but only average last X # of values

    Try something like this...

    Data Range
    A
    B
    C
    D
    E
    1
    2
    tom
    85
    Ed
    58
    3
    tom
    88
    4
    tom
    93
    5
    bob
    66
    6
    ed
    86
    7
    tom
    101
    8
    tom
    232
    9
    ed
    44
    10
    bob
    99
    11
    bob
    87
    12
    33
    13
    ed
    44
    14
    bob
    72
    15
    ------
    ------
    ------
    ------
    ------


    This array formula** entered in E2:

    =AVERAGE(IF(ROW(A2:A14)>=LARGE(IF(A2:A14=D2,ROW(A2:A14)),3),IF(A2:A14=D2,B2:B14)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    For your application change the 3 to 10.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Averageif, but only average last X # of values

    1) Create a couple of named ranges to make th formulas easy to read.
    - Press CTRL-F3 to open the Name Manager
    - Add the following two named ranges:
    >> MyKeys: =OFFSET(Sheet1!$A$1, , , COUNTA(Sheet1!$A:$A), ) (change the sheet name as needed)
    >> MyValues: =OFFSET(MyKeys, , 1, , )

    2) Now, assuming one value to Average by is in cell G3, the array formula will now work:
    =AVERAGE(AVERAGE(LARGE(IF(MyKeys=G3, MyValues), {1,2,3,4,5,6,7,8,9,10})))
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    1
    Key
    Value
    2
    Cat
    1
    3
    Cat
    1
    cat
    5
    4
    Cat
    1
    5
    Cat
    1
    6
    Cat
    1
    7
    Cat
    1
    8
    Cat
    1
    9
    Cat
    1
    10
    Cat
    1
    11
    Cat
    1
    12
    Cat
    2
    13
    Cat
    2
    14
    Cat
    2
    15
    Cat
    2
    16
    Cat
    2
    17
    Cat
    2
    18
    Cat
    2
    19
    Cat
    2
    20
    Cat
    2
    21
    Cat
    2
    22
    Cat
    5
    23
    Cat
    5
    24
    Cat
    5
    25
    Cat
    5
    26
    Cat
    5
    27
    Cat
    5
    28
    Cat
    5
    29
    Cat
    5
    30
    Cat
    5
    31
    Cat
    5
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    09-30-2012
    Location
    Madison, WI
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Averageif, but only average last X # of values

    Thanks! I started working off of Tony's because that came in first. I have a new wrinkle. I lied when I said which column I'd be matching against. I'm working with sports statistics, and column B has the player names, but column AZ has a unique player ID created by combining the cells with their name, position, and team. I have many columns pulling data referencing this unique player ID, but for some reason this formula won't work with that, it returns #N/A. The formula works fine when searching by the player name alone. I've triple checked to make sure it's not a reference error from changing between searching for the player name or player ID. It's not a problem with the player ID, because a simple Averageif formula works fine when referencing this. It's also not an issue with the formula creating the player ID, because I copied it and pasted it back as a value and that didn't fix the issue. It's driving me nuts because a test scenario I created worked fine, but the real one won't. Here is the adapted formula I'm using, as well as the simple Averageif formula which works fine.

    =AVERAGE(IF(ROW(Results!B:B)>=LARGE(IF(Results!B:B=AZ2,ROW(Results!B:B)),10),IF(Results!B:B=AZ2,Results!AK:AK)))

    I used CTRL+SHIFT+ENTER.

    =AVERAGEIF(Results!B:B,AZ2,Results!AK:AK)

    I'll have to try Jerry's next. I can't for the life of me figure out why this won't work. I also run a countif to see the # of games played and there are plenty of entries according to this formula. Plenty = many more than 10.

    =IF(A2="","",COUNTIF(Results!$B:$B,$AZ2))

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Averageif, but only average last X # of values

    Quote Originally Posted by smatchymo View Post
    =AVERAGE(IF(ROW(Results!B:B)>=LARGE(IF(Results!B:B=AZ2,ROW(Results!B:B)),10),IF(Results!B:B=AZ2,Results!AK:AK)))
    Referencing entire columns won't cause an error but you should avoid doing that as it's very inefficient (slow to calculate).

    If you can't get it to work post a SMALL sample file (20 rows worth of data is plenty) so we can see what the problem may be.

  6. #6
    Registered User
    Join Date
    09-30-2012
    Location
    Madison, WI
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Averageif, but only average last X # of values

    I had the same problem with Jerry's formula but figured out the issue. Column B on the Results spreadsheet has the occasional #N/A in it which doesn't seem to bother the regular Averageif formula. I've created a new formula to prevent those. Thanks for the help!

  7. #7
    Registered User
    Join Date
    09-30-2012
    Location
    Madison, WI
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Averageif, but only average last X # of values

    Thanks for the tip Tony. I'm working on an NBA spreadsheet. Currently there are 11,265 rows and I have no clue how many there will be by seasons end. I was hoping for a formula where I wouldn't have to go CTRL+SHIFT+ENTER because I know those can be slow. I've learned some other tricks since my NFL spreadsheet though and should be able to manage. The NFL sheet takes 40 minutes to calculate when loading fresh data each week... But since substituting Vlookup with Index/Match, as well as combining cells to create unique player ID's instead of using a complex Index/Match formula matching against two criteria (Player Name & Team), I've been able to build a much quicker NBA spreadsheet.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Averageif, but only average last X # of values

    Quote Originally Posted by smatchymo View Post
    Currently there are 11,265 rows and I have no clue how many there will be by seasons end.
    Use dynamic ranges or refer to a range that is bigger than you think you'll need but less than the entire column.

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Averageif, but only average last X # of values

    Good deal. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Trailing Average Using AverageIf in Excel
    By atomant2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-21-2014, 12:45 AM
  2. [SOLVED] AVERAGEIF Vs AVERAGE(IF()) not return the same result
    By Faridwahidi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-01-2014, 02:07 PM
  3. Averageif function with Average range subtracting columns
    By miguelcristovao in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 07-18-2014, 04:56 PM
  4. AVERAGEIF - calculate monthly average
    By TheRobsterUK in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-05-2014, 11:53 AM
  5. AVERAGEIF Only Perform the Average if a Filter is Met
    By RossThompson87 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-06-2013, 07:53 AM
  6. [SOLVED] AVERAGEIF Formula Different then Excel Average
    By Winfield in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-30-2013, 09:13 PM
  7. Another Average or AverageIf Issue
    By scarames in forum Excel General
    Replies: 6
    Last Post: 08-18-2009, 11:09 AM

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