Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
MIKE0W
 
Posts: n/a
Default 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   Report Post  
Biff
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP returning wrong row jthomas Excel Worksheet Functions 6 August 3rd 05 10:32 PM
test forumla rather than result Ruthki Excel Worksheet Functions 2 July 6th 05 11:15 PM
Vlookup returning No data. Alex H Excel Worksheet Functions 4 July 3rd 05 09:08 PM
Returning all values from a lookup - not just the first/last one Jim Burns Excel Worksheet Functions 2 June 20th 05 04:04 PM
MY VLOOKUP IS RETURNING N/A AND THEREFORE MY TOTAL SUM AT THE BOT. Martin Excel Discussion (Misc queries) 2 December 13th 04 12:21 PM


All times are GMT +1. The time now is 01:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"