The following array formula will return the average of the 5
largest values in B1:B20:

=AVERAGE(LARGE(B1:B20,ROW(INDIRECT("1:5"))))

Since this is an array formula, you must press CTRL+SHIFT+ENTER
rather than just ENTER when you first enter the formula and when
you edit it later. If you do this, Excel will display the formula
in the formula bar enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Phillycheese5"

wrote in message
news:Phillycheese5.20ayco_1135021204.1445@excelfor um-nospam.com...

I'm looking for the formula that will use the largest 5 values
in column
A and average the corresponding values in column B. I tried
this...

=AVERAGE(IF(A1:A500LARGE(A1:A500,5),B1:B500))

which gave me an answer which was close, but not exact as to
when I did
it manually; so something is off.

Any help would be appreciated.
Thanks,
Phillycheese5


--
Phillycheese5
------------------------------------------------------------------------
Phillycheese5's Profile:
http://www.excelforum.com/member.php...o&userid=24196
View this thread:
http://www.excelforum.com/showthread...hreadid=494679