Average excluding top 5% of values for specific dates and one additional criterion

[Losttexan]

Normally can find the answers to my questions within this forum but have been searching off and on for a couple of days without luck (yet). Hoping you can help!

I am trying to calculate the average of Value/column C below per Date/column A only when Type/column B = 1 and excluding the top 5% of numerical results in Value/column C.

The example below is simplified, with only 20 Type 1 values per date but I could have many hundreds or thousands of that type on any given date. Using this sample, I want to find the average of 'Value' for all Type 1 entries where Date = May-01-2017, excluding the first row (5745) because it is in the top 5% of values for that date and type.

I've tried multiple approaches (nested IF arrays/AverageIFs/SumIFs trying to use the LARGE function within that array) but always keep coming back only with the overall average of Type 1s for each date. Example:

=AVERAGEIFS(A2:A41,May-01-2017,B2:B41,1,C2:C41,"<"&LARGE(C2:C41,COUNTIFS(B2:B41,1,A2:A41,May-01-2017)*0.05))

Not sure I translated all of the parentheses above properly when extracting it from my source but each attempt of this type returns the overall average of Type = 1 for the specified date. I think the LARGE function isn't being properly constrained into the array of "Date = May-01-2017 and Type = 1".

None of the data cells will contain null values although zeroes are frequently present.

Please let me know if I can provide an additional information and thank you in advance.

Date	Type	Value
May-01-2017	1	5745
May-01-2017	1	1064
May-01-2017	1	232
May-01-2017	1	108
May-01-2017	1	99
May-01-2017	1	48
May-01-2017	1	109
May-01-2017	1	284
May-01-2017	1	182
May-01-2017	1	190
May-01-2017	1	558
May-01-2017	1	141
May-01-2017	1	147
May-01-2017	1	188
May-01-2017	1	1414
May-01-2017	1	256
May-01-2017	1	89
May-01-2017	1	304
May-01-2017	1	0
May-01-2017	1	0
May-01-2017	2	9
May-01-2017	2	14
May-01-2017	2	186
May-01-2017	2	512
May-01-2017	2	16125
May-01-2017	2	1414
May-01-2017	2	8214
May-01-2017	2	3614
May-01-2017	2	85
May-01-2017	2	0
May-01-2017	2	5979
May-01-2017	2	3507
May-01-2017	2	232
May-01-2017	2	478
May-01-2017	2	1938
May-01-2017	2	5663
May-01-2017	2	14
May-01-2017	2	37
May-01-2017	2	426
May-01-2017	2	1447
May-02-2017	1	6125
May-02-2017	1	14
May-02-2017	1	0
May-02-2017	1	0
May-02-2017	1	19
May-02-2017	1	187
May-02-2017	1	223
May-02-2017	1	414
May-02-2017	1	370
May-02-2017	1	16
May-02-2017	1	0
May-02-2017	1	29
May-02-2017	1	37
May-02-2017	1	177
May-02-2017	1	806
May-02-2017	1	24
May-02-2017	1	99
May-02-2017	1	704
May-02-2017	1	91
May-02-2017	1	400
May-02-2017	2	23
May-02-2017	2	1847
May-02-2017	2	9154
May-02-2017	2	14
May-02-2017	2	0
May-02-2017	2	808
May-02-2017	2	627
May-02-2017	2	505
May-02-2017	2	413
May-02-2017	2	399
May-02-2017	2	211
May-02-2017	2	197
May-02-2017	2	8
May-02-2017	2	0
May-02-2017	2	27
May-02-2017	2	9414
May-02-2017	2	82
May-02-2017	2	187
May-02-2017	2	8714

<tbody>
</tbody>

 

[Eric W]

Welcome to the forum.

Here's your problem:

=AVERAGEIFS(A2:A41,May-01-2017,B2:B41,1,C2:C41,"<"&LARGE(C2:C41,COUNTIFS(B2:B41,1,A2:A41,May-01-2017)*0.05))

You're properly finding that there are 20 matching rows, and 20 * .05 =1, but then you're using the entire range in the LARGE, so you're getting the largest value (16125), even though it's not a matching row. So 5745 would be included.


You could try something like this:
=AVERAGEIFS(C2:C41,A2:A41,DATE(2017,4,1),B2:B41,1,C2:C41,"<"&LARGE(IF((A2:A41=DATE(2017,4,1))*(B2:B41)=1,C2:C41),COUNTIFS(B2:B41,1,A2:A41,DATE(2017,4,1))*0.05))

confirmed with Control+Shift+Enter.

Note the C2:C41 at the start of the formula, and I used different a different date function. Use what works for you.

 

[Losttexan]

Eric,

I think you nailed it... working as expected once I included your conditions within the LARGE formula. Thanks!

 

[Eric W]

Glad to help!

Thanks for the feedback.