Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Select Largest 5 in A, AVG values in B
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Select Largest 5 in A, AVG values in B
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Select Largest 5 in A, AVG values in B
You have to use greater than or equal to, instead of just greater than:
=AVERAGE(IF(A1:A500=LARGE(A1:A500,5),B1:B500)) Still array entered (CTRL+SHIFT+ENTER) -- Regards, Dave "Phillycheese5" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Select Largest 5 in A, AVG values in B
Nothing beats self-teaching... Apparently I need to say "greater than the 6th largest value" which would give me the largest 5. In case anyone was interested, here is the formula that works: =AVERAGE(IF(B3:B500LARGE(B3:B500,6),D3:D500)) ...use CSE. -- Phillycheese5 ------------------------------------------------------------------------ Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196 View this thread: http://www.excelforum.com/showthread...hreadid=494679 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Select Largest 5 in A, AVG values in B
On Mon, 19 Dec 2005 13:39:11 -0600, Phillycheese5
wrote: 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 Something like: =SUMIF(A1:A500,"="&LARGE(A1:A500,5),B1:B500) / 5 --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Min values in a list of numbers | Excel Worksheet Functions | |||
Cells User Select Locked after upgrade to Excel 2002 | Excel Discussion (Misc queries) | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
how can I select two different values for a cell? | Excel Worksheet Functions | |||
Ranking cells largest to smallest | Excel Discussion (Misc queries) |