Returning Forumla For Result

[MIKE0W]

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.

 

[Biff]

Hi!

For question 1:

Fix your average formula so that it doesn't return #DIV/0!:

=IF(COUNT(D37)=0,"",AVERAGE(D37))

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),INDEX($C1:$F1,SMALL(IF($C2:$F2=MAX($C2:$F2),COLUMN($A:$D)),COLUMN(A:A))),"")

Copy across until you get blank cells.

Biff