5 highest - but return average value from different column

[Jane]

Hi All


I have tried to look around the website but can't seem to find a solution to my challenge - I can only find part of the solution. I have looked at average, range, large and other thing.


My data looks like this:


Column D

Square meters

100

160

100

150

125

110

105


Column G

Price

250

240

200

210

230

200

250


Question: I want to know the average price for the 5 highest square meters.

* 5 highest square meters: 160, 150, 125, 110 and 105

* prices corresponding to these square meters: 240, 210, 230, 200 and 250

* so average of 240, 210, 230, 200 and 250 = 226


So 226 is the answer that I want, but how do I write the formula for this?


The data area is dynamic and more rows will be added in time, but I always want the average price for the 5 highest square meters.


Hope someone can help...

Thanks in advance


Best Regards

Jane

 

[Hui]

Jane

have you tried:

=SUMPRODUCT(1*($D$2:$D$8>=LARGE($D$2:$D$8,5))*($G$2:$G$8))/5


Or this one which will adjust as you add data

=SUMPRODUCT(1*(OFFSET(D1,1,0,COUNT($D:$D),1)>=LARGE(OFFSET(D1,1,0,COUNT($D:$D),1),5))*(OFFSET(G1,1,0,COUNT($D:$D),1)))/5

In this case make sure there are no numbers below the bottom of your data


If you want the top 4 or 6 items change the two 5's as appropriate

 

[Jane]

Hi Hui


That is perfect


Thank you very much...


Best Regards

Jane