Finding AVERAGEIFS for just the 5 Highest Values in a Set

Question

THE PROBLEM

I'm trying to get the Average Price if these criteria are met...

1. Search Term = "cup"
2. Top Competitor = "TRUE"
3. I'd only like to average the Top 5 results (by Search Ranking) in the set.

So the formula should return 8.00

WHAT I'VE TRIED

=AVERAGEIFS(C:C,A:A,"cup",D:D,"TRUE",B:B,"<="&SMALL(B:B,5))

My formula is returning 7.5, which is too low. The formula seems to work until I add in the last criteria: B:B,"<="&SMALL(B:B,5)

	A	B	C	D
1	Search Term	Rank	Price	Top Competitor
2	cup	1	5.00	TRUE
3	cup	2	5.00	FALSE
4	cup	3	10.00	TRUE
5	cup	4	5.00	TRUE
6	cup	5	5.00	FALSE
7	cup	6	10.00	FALSE
8	cup	7	10.00	TRUE
9	cup	8	10.00	TRUE
10	cup	9	5.00	TRUE
11	cup	10	5.00	TRUE
12	plate	1	20.00	TRUE
13	plate	2	10.00	FALSE
14	plate	3	10.00	TRUE
15	plate	4	20.00	TRUE
16	plate	5	10.00	FALSE
17	plate	6	20.00	FALSE
18	plate	7	20.00	FALSE
19	plate	8	20.00	FALSE
20	plate	9	15.00	TRUE

Thanks for your help!

Answer 1

The problem is that you need to apply the ranking parameter AFTER you have applied the cup and true parameters.

You did not indicate what you want to happen if there are not 5 items that meet your Search Term and Top Competitor requirements. So both of these formulas will return an error for plate since you only have four (4) TRUE's

In Excel Office 365, you can use:

=LET(x,FILTER(tbl,(tbl[Search Term]="cup")*tbl[Top Competitor]),AVERAGE(FILTER(INDEX(x,0,3),INDEX(x,0,2)<=SMALL(INDEX(x,0,2),5))))

where you first filter by cup and true; and then look at the top five of the filtered result.

If you have an earlier version, you can try this formula:

=AVERAGE(AVERAGEIFS(tbl[Price],tbl[Rank],AGGREGATE(15,6,1/((tbl[Search Term]="cup")*tbl[Top Competitor])*tbl[Rank],{1;2;3;4;5}),tbl[Search Term],"cup",tbl[Top Competitor],TRUE))

Note that in both formulas, I used a Table with structured references. You can, of course, use regular references if you prefer.

The second formula will return an array of values, which are the one's to be averaged.

In some earlier versions of Excel, you may need to "confirm" this array-formula by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar

Answer 2

Ok, so just to show the bit I think you have wrong, I suggest you use large() like so:

=AVERAGE(LARGE(A1:A10,{1,2,3,4,5}))

large takes to top 5 values and average then takes the average, you can add the other constraints.