CODEWITHSUNDEEP
excelexcel-formula
phxdrupal
phxdrupal

Reputation: 13

Finding AVERAGEIFS for just the 5 Highest Values in a Set

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!

Upvotes: 1

Views: 663

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

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

Upvotes: 2

Related Questions