• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

List All Names with Max Value in a Row

R

raisc12

New Member
Hi,


Below is the format of my excel file. Basically, I have skills in the first column and the suceeding columns are rating (1 lowest and 5 highest) of each person on a particular skill. I have several skills in line but i only listed some for the sake of this sample. What I want to do is to have list of names for a particular skills which has the highest rating. For instance in "Dancing" skill, Charles and Honey has the highest rating. Please help me create a formula that will fetch both Charles and Honey (having highest rating among others) for the "Dancing" skill.


Skills Andy Bert Charles Darwin Emman Fred Gaile Honey

Dancing 2 3 5 4 2 1 4 5

Singing 3 1 4 2 2 3 1 3

Acting 2 1 3 1 3 2 4 1


Thanks in advance!
 
Array formula:

=IF(COUNTIF($B2:$G2,MAX($B2:$G2))>COLUMNS($A1:A1),"",INDEX($1:$1,SMALL(IF($B2:$G2=MAX($B2:$G2),COLUMN($B2:$G2)),COLUMN(A$1))))


Copy formula to the right as far as would ever be needed to list all names. To see a discussion about how this type of formula works, see:

http://chandoo.org/forums/topic/how-to-retrieve-the-na-value
 
Thanks Luke. I revised the formula a bit and use this formula instead.


=IF(B2=MAX($B2:$G2),INDEX(1:1,1,IF($B2=MAX($B2:$G2),COLUMN($B2:$G2),COLUMN(B$1))),"")
 
Glad it's working, but I'm not sure your formula is actually doing what you think it is doing. You IF statement is set to either return an array of numbers (true), or a single number (false). In which case, how the INDEX function handles it will be unstable. Guessing at where you were going, perhaps:

=IF(B2=MAX($B2:$G2),B2,"")

is what you were going for?
 
i simplified the formula to:

=IF(B2=MAX($B2:$G2),INDEX($1:$1,1,COLUMN(B2:$G2)),"").. seems working though.


honestly, i'm having problem with your index formula and i can't seem to figure out how to fix it. i only used the INDEX formula now so i'm still exploring how to actually use it.


the next step i'm thingking is to eliminate the "" values and just look up and fetch the names which only have the max value in the row. besides, i'm only interested to get the names of those who have max score.


i am now reading a lot more with match, offset, and find since, like index, i'm still exploring these formula. i had a feeling those can also help but not sure though.
 
also, i tried this formula:

=IF(B2=MAX($B2:$G2),INDEX($1:$1,1,MATCH(MAX($B2:$G2),$B2:$G2,0)),"")


now my problem is how to match the second occurence in the row. hope you can help me on this. thanks in advance luke! :)
 
First, I need to correct my original formula. Start should be changed, needs to be:

=IF(COUNTIF($B2:$G2,MAX($B2:$G2))<COLUMNS($A1:A1),"",INDEX($1:$1,SMALL(IF($B2:$G2=MAX($B2:$G2),COLUMN($B2:$G2)),COLUMN(A$1))))


Where this section:

=IF(COUNTIF($B2:$G2,MAX($B2:$G2))<COLUMNS($A1:A1),""


is the error handler, or what to do when you've already displayed all the names that match your criteria. Now we'll look at latter half, the real "work horse".

INDEX($1:$1,SMALL(IF($B2:$G2=MAX($B2:$G2),COLUMN($B2:$G2)),COLUMN(A$1))))


Starting on outside, we're setting up an INDEX function. However, we need to give it only a single number input for the row/column index. This is why the SMALL function is critical. Using the COLUMN(A$1) to increment (1st smallest, 2nd smallest, etc), the SMALL function takes the output of the IF function (which is spitting out all the row numbers of data that matches our criteria) and takes the 1st smallest number. In the next cell/formula, the end now says COLUMN(B$2), which evaluates to 2, so it takes the 2nd smallest number.


Does that help?
 
You must log in or register to reply here.
Back
Top