5 highest - but return average value from different column

Jane

New Member
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...

Best Regards

Jane

Hui

Excel Ninja
Staff member
Jane

have you tried:

=SUMPRODUCT(1*(\$D\$2:\$D\$8&#62;=LARGE(\$D\$2:\$D\$8,5))*(\$G\$2:\$G\$8))/5

=SUMPRODUCT(1*(OFFSET(D1,1,0,COUNT(\$D:\$D),1)&#62;=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

New Member
Hi Hui

That is perfect

Thank you very much...

Best Regards

Jane