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

Copy across until you get blank cells.

Biff

"MIKE0W" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>