If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Different perspective of RANK
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. |
#3
|
|||
|
|||
Different perspective of RANK
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. . |
#4
|
|||
|
|||
Different perspective of RANK
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 - |
#5
|
|||
|
|||
Different perspective of RANK
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 - |
Thread Tools | |
Display Modes | |
|
|