Guru help needed!!!

[Skebo]

Hi All,

I have a table that looks something like this

Top Perf|Judy|Paul|Mary|Mark
Mark | 1| 0| 3| 5
Judy | 3| 1| 1| 3
Judy | 0| 0| 0| 0

Under "Top Perf" I have the following formula:
=INDIRECT("R1"&"C"&1+MATCH(MAX(B3:G3),B3:G3,0),FALSE)

This works fine for the first row of data, where it returns "Mark". I want to change the results for the last two rows.
1.) In cases where there is a tie I would like both names displayed in the same cell(separated by a comma if possible).
2.) In cases where there are all zeros, return #NA. MATCH returns the first 0 as the maximum value right now. These cells can not be left blank instead of containing a 0.
3.) When any one surpasses a threshold value of 10, I want all of those names displayed instead.

I hope this is clear and thanks in advance.



[ This Message was edited by: Skebo on 2002-03-08 12:09 ]

[ This Message was edited by: Skebo on 2002-03-08 13:02 ]

[ This Message was edited by: Skebo on 2002-03-10 19:11 ]

[Skebo]

I'm still stuck on this. Anyone? Anyone?

[Corni]

Try this formula:

=IF(AND(B2=0,C2=0,D2=0,E2=0),"#NA",IF(OR(B2>10,B2=MAX(B2:E2)),OFFSET(B2,1-ROW(B2),0)&",",""))&IF(SUM(B2:E2)=0,"",IF(OR(C2>10,C2=MAX(B2:E2)),OFFSET(C2,1-ROW(C2),0)&",",""))&IF(SUM(B2:E2)=0,"",IF(OR(D2>10,D2=MAX(B2:E2)),OFFSET(D2,1-ROW(D2),0)&",",""))&IF(SUM(B2:E2)=0,"",IF(OR(E2>10,E2=MAX(B2:E2)),OFFSET(E2,1-ROW(E2),0),""))

As you have only four names, the range should be B2:E2 and not B2:G2.

[ This Message was edited by: Corni on 2002-03-10 22:00 ]

[Aladin Akyurek]

On 2002-03-08 12:09, Skebo wrote:
Hi All,

I have a table that looks something like this

Top Perf|Judy|Paul|Mary|Mark
Mark | 1| 0| 3| 5
Judy | 3| 1| 1| 3
Judy | 0| 0| 0| 0

Under "Top Perf" I have the following formula:
=INDIRECT("R1"&"C"&1+MATCH(MAX(B3:G3),B3:G3,0),FALSE)

This works fine for the first row of data, where it returns "Mark". I want to change the results for the last two rows.
1.) In cases where there is a tie I would like both names displayed in the same cell(separated by a comma if possible).
2.) In cases where there are all zeros, return #NA. MATCH returns the first 0 as the maximum value right now. These cells can not be left blank instead of containing a 0.
3.) When any one surpasses a threshold value of 10, I want all of those names displayed instead.

I hope this is clear and thanks in advance.



[ This Message was edited by: Skebo on 2002-03-08 12:09 ]

[ This Message was edited by: Skebo on 2002-03-08 13:02 ]

[ This Message was edited by: Skebo on 2002-03-10 19:11 ]

Skebo,

It took me some time to come back to this hard question.

First of all you need Longre's morefunc.xll, which contains a UDF called MCONCAT that I use in what follows:

I'll assume that A1:E4 houses the sample data you provided where A2:A4 must show results (top performers), concatenated together with a comma as separator.

In G2 enter:

=IF(SUM($B2:$E2)>0,IF(B2=MAX($B2:$E2),INDIRECT(ADDRESS(1,COLUMN(B2)),4),""),"")

Copy down this till row 4 then across as many columns as you have in your data range.

In A2 array-enter and copy down till row 4:

=TRIM(SUBSTITUTE(IF(SUM(B2:E2),MCONCAT(G2:J2,IF(LEN(G2:J2),",","")),MCONCAT($B$1:$E$1,",")),",,",","))

Note. You need to hit control+shift+enter at the same time, not just enter, to array-enter a formula.

I know the solution is a bit demanding. Until there is something better on offer, you might want to use it.

The data plus the results areas will look as follows:

{"Top Perf","Judy","Paul","Mary","Mark ","","","","","";
"Mark",1,0,3,5,0,"","","","Mark ";
"Judy,Mary,Mark",3,1,3,3,0,"Judy","","Mary","Mark ";
"Judy,Paul,Mary,Mark",0,0,0,0,0,"","","",""}

You can get morefunc at:

http://perso/wanadoo.fr/longre/excel/downloads/

Aladin



[ This Message was edited by: Aladin Akyurek on 2002-03-12 13:18 ]

[ This Message was edited by: Aladin Akyurek on 2002-03-12 13:19 ]

[Skebo]

Thanks Aladin. It works for the most part. This gives me something to build upon. If guess parts 2 and 3 can be covered by an if then statement.
You may be interested to know that if 'Judy' isn't among a tie for top performer, then the names returned are not comma separated. ie values of 0,0,5,5 will return "MaryMark", whereas 5,0,5,5 will return "Judy,Mary,Mark". No biggy. Just an FYI.

[Aladin Akyurek]

On 2002-03-12 16:15, Skebo wrote:
Thanks Aladin. It works for the most part. This gives me something to build upon. If guess parts 2 and 3 can be covered by an if then statement.
You may be interested to know that if 'Judy' isn't among a tie for top performer, then the names returned are not comma separated. ie values of 0,0,5,5 will return "MaryMark", whereas 5,0,5,5 will return "Judy,Mary,Mark". No biggy. Just an FYI.

Skebo,

I admit I didn't check thorougly the behavior of MCONCAT. I had to solve e.g., the multiple comma problem that I ecountered as in ",,,Mark" with using SUBSTITUTE. Maybe you could look more closely at this function to solve the absent comma problem.

Aladin