software

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:A500gt;LARGE(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: www.excelforum.com/member.php...oamp;userid=24196
View this thread: www.excelforum.com/showthread...hreadid=494679The following array formula will return the average of the 5
largest values in B1:B20:

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

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

quot;Phillycheese5quot;
gt;
wrote in message
news:Phillycheese5.20ayco_1135021204.1445@excelfor um-nospam.com...
gt;
gt; I'm looking for the formula that will use the largest 5 values
gt; in column
gt; A and average the corresponding values in column B. I tried
gt; this...
gt;
gt; =AVERAGE(IF(A1:A500gt;LARGE(A1:A500,5),B1:B500))
gt;
gt; which gave me an answer which was close, but not exact as to
gt; when I did
gt; it manually; so something is off.
gt;
gt; Any help would be appreciated.
gt; Thanks,
gt; Phillycheese5
gt;
gt;
gt; --
gt; Phillycheese5
gt; ------------------------------------------------------------------------
gt; Phillycheese5's Profile:
gt; www.excelforum.com/member.php...oamp;userid=24196
gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=494679
gt;
You have to use greater than or equal to, instead of just greater than:

=AVERAGE(IF(A1:A500gt;=LARGE(A1:A500,5),B1:B500))

Still array entered (CTRL SHIFT ENTER)

--
Regards,
Davequot;Phillycheese5quot; wrote:

gt;
gt; I'm looking for the formula that will use the largest 5 values in column
gt; A and average the corresponding values in column B. I tried this...
gt;
gt; =AVERAGE(IF(A1:A500gt;LARGE(A1:A500,5),B1:B500))
gt;
gt; which gave me an answer which was close, but not exact as to when I did
gt; it manually; so something is off.
gt;
gt; Any help would be appreciated.
gt; Thanks,
gt; Phillycheese5
gt;
gt;
gt; --
gt; Phillycheese5
gt; ------------------------------------------------------------------------
gt; Phillycheese5's Profile: www.excelforum.com/member.php...oamp;userid=24196
gt; View this thread: www.excelforum.com/showthread...hreadid=494679
gt;
gt;


Nothing beats self-teaching...

Apparently I need to say quot;greater than the 6th largest valuequot; which
would give me the largest 5. In case anyone was interested, here is
the formula that works:

=AVERAGE(IF(B3:B500gt;LARGE(B3:B500,6),D3500)) ...use CSE.--
Phillycheese5
------------------------------------------------------------------------
Phillycheese5's Profile: www.excelforum.com/member.php...oamp;userid=24196
View this thread: www.excelforum.com/showthread...hreadid=494679On Mon, 19 Dec 2005 13:39:11 -0600, Phillycheese5
gt; wrote:

gt;
gt;I'm looking for the formula that will use the largest 5 values in column
gt;A and average the corresponding values in column B. I tried this...
gt;
gt;=AVERAGE(IF(A1:A500gt;LARGE(A1:A500,5),B1:B500))
gt;
gt;which gave me an answer which was close, but not exact as to when I did
gt;it manually; so something is off.
gt;
gt;Any help would be appreciated.
gt;Thanks,
gt;Phillycheese5Something like:

=SUMIF(A1:A500,quot;gt;=quot;amp;LARGE(A1:A500,5),B1:B500) / 5--ron