+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 21 of 21

Formula With Nth Values Using AVERAGEIF With Criteria

  1. #16
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    2010 & 2016
    Posts
    4,415

    Re: Formula With Nth Values Using AVERAGEIF With Criteria

    Have a look at post #13

    BSB
    Please show appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

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

    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
    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.

  3. #18
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    2010 & 2016
    Posts
    4,415

    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.

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

    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?

  5. #20
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    2010 & 2016
    Posts
    4,415

    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

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

    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
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

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

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