Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Returning Forumla For Result
Two questions: 1. i am using match and index in a forumla. I am looking up the max value in one row and returning the persons name with the highest average in the selected cell. It works however for someone where no imput has been given to compute the average in the average column it results divided by 0. When it looks up the max for the column it finds this over the greatest value. How do I exclude forumlas from the result and find the highest value? 2. If there are two values or more equal to each other (Ex: 3 people have a 90 average) it returns the name of the first one it finds is there a way to return all names seperated by a : just wondering? Thanks in advance for any help. -- MIKE0W ------------------------------------------------------------------------ MIKE0W's Profile: http://www.excelforum.com/member.php...o&userid=21465 View this thread: http://www.excelforum.com/showthread...hreadid=396726 |
#2
|
|||
|
|||
Hi!
For question 1: Fix your average formula so that it doesn't return #DIV/0!: =IF(COUNT(D3:D7)=0,"",AVERAGE(D3:D7)) For question 2: Assume names are in the range C1:F1 Averages are in the range C2:F2 To extract all ties with max average: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =IF(COUNTIF($C2:$F2,MAX($C2:$F2))=COLUMNS($A:A),I NDEX($C1:$F1,SMALL(IF($C2:$F2=MAX($C2:$F2),COLUMN( $A:$D)),COLUMN(A:A))),"") Copy across until you get blank cells. Biff "MIKE0W" wrote in message ... Two questions: 1. i am using match and index in a forumla. I am looking up the max value in one row and returning the persons name with the highest average in the selected cell. It works however for someone where no imput has been given to compute the average in the average column it results divided by 0. When it looks up the max for the column it finds this over the greatest value. How do I exclude forumlas from the result and find the highest value? 2. If there are two values or more equal to each other (Ex: 3 people have a 90 average) it returns the name of the first one it finds is there a way to return all names seperated by a : just wondering? Thanks in advance for any help. -- MIKE0W ------------------------------------------------------------------------ MIKE0W's Profile: http://www.excelforum.com/member.php...o&userid=21465 View this thread: http://www.excelforum.com/showthread...hreadid=396726 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP returning wrong row | Excel Worksheet Functions | |||
test forumla rather than result | Excel Worksheet Functions | |||
Vlookup returning No data. | Excel Worksheet Functions | |||
Returning all values from a lookup - not just the first/last one | Excel Worksheet Functions | |||
MY VLOOKUP IS RETURNING N/A AND THEREFORE MY TOTAL SUM AT THE BOT. | Excel Discussion (Misc queries) |