# Formula With Nth Values Using AVERAGEIF With Criteria

1. ## Re: Formula With Nth Values Using AVERAGEIF With Criteria

Have a look at post #13

BSB

2. ## 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?

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

Originally Posted by swordswinger710
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'.

4. ## 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. ## 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. ## 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?

Page 2 of 2 First 1 2

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

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