Different perspective of RANK

I am trying to solve a problem I am having with RANK. I found some great
information that is posted on “cpearson.com� and I see what it does but the
formulas have to be in the same rows that the data is in. What I am looking
for is to populate the 1st place for a caption in a separate row.
“=OFFSET(B$11,MATCH(SMALL(K$11:K$13,ROW()-ROW(K$11)+1),K$11:K$13,0)-1,0)�
works if data is in same row.
Info:
Column B ColumnK
Name Rank
Group1 2
Group2 1
Group3 3

Now in a row below the above data and in a separate column I want to display
the name that ranked 1st. ie Group2.

And finally if there is a tie for first in the data I want to display
“Group2 and Group(Tied Group)� in tha same cell.

You could put this in, say, L5 if your data is in column B and column
K as in your example:

=INDEX(B:B,MATCH(1,K:K,0))

It will return the name from column B which has a rank of 1 in column
K.

Hope this helps.

Pete

On Nov 17, 7:36*pm, swansonray
wrote:
I am trying to solve a problem I am having with RANK. *I found some great
information that is posted on “cpearson.com” and I see what it does but the
formulas have to be in the same rows that the data is in. What I am looking
for is to populate the 1st place for a caption in a separate row.
“=OFFSET(B$11,MATCH(SMALL(K$11:K$13,ROW()-ROW(K$11)+1),K$11:K$13,0)-1,0)”
works if data is in same row.
Info:
Column B * * * * * * * * * * * ColumnK
Name * * * * * *Rank
Group1 * * * * *2
Group2 * * * * *1
Group3 * * * * *3

Now in a row below the above data and in a separate column I want to display
the name that ranked 1st. ie Group2.

And finally if there is a tie for first in the data I want to display
“Group2 and Group(Tied Group)” in tha same cell.

This does produce the said result and works if there is only 1 group ranked
1st. Thank you. But what about the second scenario if there are two groups
ranked 1 (tied) I would like to display "Group1 and Group2" or "Group1 &
Group2"

"Pete_UK" wrote:

You could put this in, say, L5 if your data is in column B and column
K as in your example:

=INDEX(B:B,MATCH(1,K:K,0))

It will return the name from column B which has a rank of 1 in column
K.

Hope this helps.

Pete

On Nov 17, 7:36 pm, swansonray
wrote:
I am trying to solve a problem I am having with RANK. I found some great
information that is posted on “cpearson.com� and I see what it does but the
formulas have to be in the same rows that the data is in. What I am looking
for is to populate the 1st place for a caption in a separate row.
“=OFFSET(B$11,MATCH(SMALL(K$11:K$13,ROW()-ROW(K$11)+1),K$11:K$13,0)-1,0)�
works if data is in same row.
Info:
Column B ColumnK
Name Rank
Group1 2
Group2 1
Group3 3

Now in a row below the above data and in a separate column I want to display
the name that ranked 1st. ie Group2.

And finally if there is a tie for first in the data I want to display
“Group2 and Group(Tied Group)� in tha same cell.

.

Do you only have 3 groups, as in your example (and formula), or could
there be a lot more in practise?

Pete

On Nov 18, 9:00*pm, swansonray
wrote:
This does produce the said result and works if there is only 1 group ranked
1st. Thank you. But what about the second scenario if there are two groups
ranked 1 (tied) I would like to display "Group1 and Group2" or "Group1 &
Group2"



"Pete_UK" wrote:
You could put this in, say, L5 if your data is in column B and column
K as in your example:

=INDEX(B:B,MATCH(1,K:K,0))

It will return the name from column B which has a rank of 1 in column
K.

Hope this helps.

Pete

On Nov 17, 7:36 pm, swansonray
wrote:
I am trying to solve a problem I am having with RANK. *I found some great
information that is posted on “cpearson.com” and I see what it does but the
formulas have to be in the same rows that the data is in. What I am looking
for is to populate the 1st place for a caption in a separate row.
“=OFFSET(B$11,MATCH(SMALL(K$11:K$13,ROW()-ROW(K$11)+1),K$11:K$13,0)-1,0)”
works if data is in same row.
Info:
Column B * * * * * * * * * * * ColumnK
Name * * * * * *Rank
Group1 * * * * *2
Group2 * * * * *1
Group3 * * * * *3

Now in a row below the above data and in a separate column I want to display
the name that ranked 1st. ie Group2.

And finally if there is a tie for first in the data I want to display
“Group2 and Group(Tied Group)” in tha same cell.

.- Hide quoted text -

- Show quoted text -

I'm away for a few days from tomorrow, so if you do reply then your
response is likely to be lost when I get back. Assuming that you do
only have 3 groups, then you could do this:

=IF(K11=1,B11&IF(OR(K12=1,K13=1)," and ",""),"")&IF(K12=1,B12&IF
(K13=1," and ",""),"")&IF(K13=1,B13,"")

This caters for all three groups being tied, for 2 out of 3 being
tied, as well as for only one group having a rank of 1.

If you have more than three groups then the above approach would be a
bit unwieldy.

Hope this helps.

Pete

On Nov 19, 1:40*am, Pete_UK wrote:
Do you only have 3 groups, as in your example (and formula), or could
there be a lot more in practise?

Pete

On Nov 18, 9:00*pm, swansonray
wrote:



This does produce the said result and works if there is only 1 group ranked
1st. Thank you. But what about the second scenario if there are two groups
ranked 1 (tied) I would like to display "Group1 and Group2" or "Group1 &
Group2"

"Pete_UK" wrote:
You could put this in, say, L5 if your data is in column B and column
K as in your example:

=INDEX(B:B,MATCH(1,K:K,0))

It will return the name from column B which has a rank of 1 in column
K.

Hope this helps.

Pete

On Nov 17, 7:36 pm, swansonray
wrote:
I am trying to solve a problem I am having with RANK. *I found some great
information that is posted on “cpearson.com” and I see what it does but the
formulas have to be in the same rows that the data is in. What I am looking
for is to populate the 1st place for a caption in a separate row.
“=OFFSET(B$11,MATCH(SMALL(K$11:K$13,ROW()-ROW(K$11)+1),K$11:K$13,0)-1,0)”
works if data is in same row.
Info:
Column B * * * * * * * * * * * ColumnK
Name * * * * * *Rank
Group1 * * * * *2
Group2 * * * * *1
Group3 * * * * *3

Now in a row below the above data and in a separate column I want to display
the name that ranked 1st. ie Group2.

And finally if there is a tie for first in the data I want to display
“Group2 and Group(Tied Group)” in tha same cell.

.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -