CODEWITHSUNDEEP
excelexcel-formulaaverage
Rudixx
Rudixx

Reputation: 105

Average top n values where next cell equals value

Having below data I would like to calculate average of top n values or top n% of values in column B where values in column A = C1.

I tried many different formulas but I can't get it to working.

{=AVERAGEIFS(B1:B14,A1:A14,C1,B1:B14,(IF(B1:B14>PERCENTILE(B1:B14,0.7),B1:B14)))}
{=AVERAGE(IF(A1:A14=C1,IF(B1:B14>PERCENTILE(B1:B14,0.7),B1:B14)))}

    A   B   C  
1   a   1   a  
2   a   1   cs  
3   cs  1   ffs  
4   a   1   .  
5   a   1   .  
6   ffs 1   .  
7   a   1   .  
8   a   1   .  
9   as  1   .  
10  a   1   .  
11  sfaq    1   .  
12  a   1   .  
13  aasf    1   .  
14  a   1   .  
15  a   1   .  
16  a   1   .  
17  qw  1   .  
.   .   .   .  
.   .   .   .  
.   .   .   .

Image of worksheet

Upvotes: 1

Views: 80

Answers (1)

barry houdini
barry houdini

Reputation: 46341

I think your AVERAGE version is on the right lines but when you calculate the PERCENTILE you also need the column A condition included within that calculation, e.g. this array formula

=AVERAGE(IF(A1:A14=C1,IF(B1:B14>PERCENTILE(IF(A1:A14=C1,B1:B14),0.7),B1:B14)))

confirmed with CTRL+SHIFT+ENTER

You can get the same result with a regular (non array) formula using AVERAGEIFS if you use AGGREGATE function to calculate the PERCENTILE, i.e. like this

=AVERAGEIFS(B1:B14,A1:A14,C1,B1:B14,">"&AGGREGATE(16,6,B1:B14/(A1:A14=C1),0.7))

I expect the two formulas to give the same results

Upvotes: 1

Related Questions