+ Reply to Thread
Results 1 to 21 of 21

Formula With Nth Values Using AVERAGEIF With Criteria

  1. #1
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Question Formula With Nth Values Using AVERAGEIF With Criteria

    I have a sheet where entries from users are listed in this format:

    A16:A = NAME
    E16:E = DATE
    AB16:AB = EFFICIENCY

    Above these cells and on the same sheet, I am setting up cells to collect this data using formulas so that the average efficiency on all identical Nth most recent dates for a specified name can be seen. This is fairly straightforward for the most recent date:

    MOST RECENT ENTRIES: =AVERAGEIFS(AB16:AB,A16:A,"FREDDY",E16:E,MAX(E16:E))

    My issue is how do I accomplish this for the remaining 4 requirements:

    2ND MOST RECENT ENTRIES
    3RD MOST RECENT ENTRIES
    4TH MOST RECENT ENTRIES
    5TH MOST RECENT ENTRIES

    Any help would be greatly appreciated, thank you!
    Last edited by swordswinger710; 06-08-2018 at 12:52 PM.
    There is so much good in the worst of us,
    And so much bad in the best of us,
    That it hardly behooves any of us
    To talk about the rest of us.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    Try

    =AVERAGEIFS(AB16:AB1000,A16:A1000,"FREDDY",E16:E1000,LARGE(E16:E1000,1))

    Change 1 to 2, 3 etc

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    Assuming you'd have more than just Freddy listed in column A, have a look at the yellow cells in the attached where I've used the below formula and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note this in an array formula and therefore needs to be confirmed with Ctrl+Shift+Enter.

    Does that help at all?

    BSB
    Attached Files Attached Files

  4. #4
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    @JohnTopley This would work great if it could count all similar DATE entries as one. If the three most recent dates are all the same, then the 1st through the 3rd most recent entries contain the same result. Is there a way to jump to the next most recent group when I change the 1?

    @BadlySpelledBuoy There are definitely more names than Freddy listed in Column A, but for some reason the array thing isn't working for me. Perhaps because I am trying to use this in Google Sheets? Is an array the only way this can be accomplished?

  5. #5
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    I'm afraid I can't help with Google Sheets as I don't use it.

    Kind of an important detail to miss out in your original post though!

    BSB

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    Yes, it's a critical bit of information, which is why we have a different sub-forum for other platforms! I've moved the thread to it.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    @ BSB @AliGW Ah, I was unaware of these things, thank you for bringing this to my attention! I do feel like @JohnTopley's solution is almost there though. Any further thoughts?

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

  9. #9
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    Well, I can provide a link for the Google Sheet. Please see here for an example.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    No, attach the sheet here, please. You can attach it as an .xls sheet.

    We have an attachment facility for a reason: nobody should have to follow a link.

  11. #11
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    I'm afraid I don't understand. This thread was moved to this location because I'm using Google Sheets. Google Sheets are online. Converting it to an Excel document kind of defeats the purpose, or am I missing something?

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    You are missing just the fact that it's easier for everybody if the file is attached here. Some people just won't help if they have to follow a link. I am trying to advise you so you get the best help, but it's up to you. If you don't want to attach it here, then don't.

  13. #13
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    I have no idea if this will work for you as I'm one of those that doesn't follow links so haven't seen your GD file.

    You may need to adapt this for your data layout but I think it works. Gives the same result as the Excel file I attached earlier.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Change the red argument for 2nd, 3rd, 4th etc. This can be done as I did earlier in the Excel file.

    BSB
    Last edited by BadlySpelledBuoy; 06-08-2018 at 02:08 PM.

  14. #14
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    If I could attach a Google Sheet, I would be happy to! BSB already attached an Excel document with a working solution, but as he pointed out, this doesn't help me because this is not an Excel issue, so saving my Google Sheet as an Excel file will accomplish just as little, unfortunately.

  15. #15
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    Quote Originally Posted by swordswinger710 View Post
    BSB already attached an Excel document with a working solution
    You can open that Excel workbook in Google Sheets and the formula will convert to the Google Sheets equivalent.

  16. #16
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    Have a look at post #13

    BSB

  17. #17
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    @63falcondude WHAT?! I did not know that. That is super helpful! Thanks for the tip!

    @BSB Thank you for your continued help - I thought it was working at first, but after adjusting some of the data in the sheet you provided, I don't think it is. Groups of similar dates aren't taken into consideration, and the calculations for some seem to be off. I attached your example to hopefully explain what I mean a bit better.

    PS Only out of curiosity - isn't opening an attachment online just as risky as clicking a link?
    Attached Files Attached Files

  18. #18
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    I can get it to work with a helper column. See attached.

    I'm sure someone far cleverer than I will jump in with a single cell formula that will involve the use of FREQUENCY or such, but this is the best I can do at the moment.

    BSB

    Quote Originally Posted by swordswinger710 View Post
    PS Only out of curiosity - isn't opening an attachment online just as risky as clicking a link?
    I didn't say my decision to not follow links was down to 'risk'.
    Attached Files Attached Files
    Last edited by BadlySpelledBuoy; 06-08-2018 at 04:06 PM.

  19. #19
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    Awesome! That should do the trick for that one, thank you for your help with that.

    Now what I'm trying to figure out is how to update this formula so that instead of averaging the specified users' percentages for the Nth largest day, it averages the specified users' percentages for the Nth largest 5 days.

    Please Login or Register  to view this content.
    Any thoughts on how this can be done by adding to/tweaking this formula?

  20. #20
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    "Nth largest 5 days"?

    You mean the last 5 from today or the last 5 according to their last entry?

    BSB

  21. #21
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    845

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    So you know how the current formula looks at Cell AC8 to determine the Nth highest value to search for? Well, it would still do that, but now instead of taking the average of all entries for one Nth highest date, it would take the average of all entries of the last 5 dates for that Nth highest value.

    For example, if one user has these dates, then this formula would calculate the average of all entries for the last 5 days, in groups of five:

    ***Oct 1, 2018
    ***Oct 2, 2018
    **Oct 3, 2018
    **Oct 5, 2018
    **Oct 5, 2018
    **Oct 5, 2018
    **Oct 14, 2018
    **Nov 1, 2018
    **Nov 2, 2018
    *Nov 7, 2018
    *Nov 8, 2018
    *Nov 10, 2018
    *Nov 10, 2018
    *Nov 11, 2018
    *Nov 12, 2018

    If AC8=1, then the average of the highest 5 dates* for the user is calculated.
    If AC8=2, then the average of the second highest 5 dates** for the user is calculated.

    Does that make sense?

+ 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. AverageIf Formula with 2 criteria
    By lostinaformula in forum Excel General
    Replies: 3
    Last Post: 03-14-2018, 05:57 PM
  2. [SOLVED] AVERAGEIF Date Criteria
    By theTaoJones in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-15-2018, 11:15 AM
  3. Averageif criteria
    By wpryan in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-03-2015, 07:11 AM
  4. Multiple Criteria AverageIF
    By orev2 in forum Excel General
    Replies: 1
    Last Post: 08-19-2014, 01:18 PM
  5. AverageIF formula Ignoring Certain values
    By jvandermolen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2014, 11:52 AM
  6. averageif formula with multiple criteria
    By CPitta in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-08-2013, 12:56 PM
  7. AverageIf based on different criteria
    By caseman in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-16-2013, 02:18 PM

Tags for this Thread

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