Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Rank Function Problem

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can anyone help with the following problem....I have Scores in Column C, and these scores are associated with data broken down into 3 subgroups in columns K, L, & M. Here is my problem...I just want to rank the scores in Column C that have the data in Columns K, L, & M in common. All this data is unsorted. Thank you all

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-08 07:58, JohnJay wrote:
    Can anyone help with the following problem....I have Scores in Column C, and these scores are associated with data broken down into 3 subgroups in columns K, L, & M. Here is my problem...I just want to rank the scores in Column C that have the data in Columns K, L, & M in common. All this data is unsorted. Thank you all
    John, the only way I can figure out how to do this is using a two step approach. Somewhere you have to specify the criteria that you are looking for in columns K, L, & M so that you can rank the scores. For my purposes, I put them in Q2, R2, and S2. In column D I put in an array formula to compare columns K, L, & M to the values in Q1, R1, and S1. The formula was (an array formula meaning you have to press CTRL+SHIFT+ENTER instead of ENTER):

    =IF(((K1:M1)=($Q$1:$S$1))*C1=0,"",((K1:M1)=($Q$1:$S$1))*C1)

    This would put the value of C1 in D1 if all three columns (K, L, & M) equal the values in Q1, R1, & S1. Then in E1 I put this formula and copied down:

    =IF(ISERROR(RANK(D1,$D$1:$D$8)),"",RANK(D1,$D$1:$D$8))

    My example was using data in rows 1 to 8. You'll have to change to the appropriate number.

    I don't know if this will help you out, but it's all I could think of.

    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    looking at that I can see that it wont work for my purpose. I was hoping that there was a way for Excel to look at three columns, say K-M, simultaniously and then rank all scores, say in column C, that have K-M in common. Since K-M can contain any string, the rank fucntion would have to isolate those scores that have K-M in common and rank them as a group.

    Thanks Barrie,

    John

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-03-08 14:31, JohnJay wrote:
    looking at that I can see that it wont work for my purpose. I was hoping that there was a way for Excel to look at three columns, say K-M, simultaniously and then rank all scores, say in column C, that have K-M in common. Since K-M can contain any string, the rank fucntion would have to isolate those scores that have K-M in common and rank them as a group.

    Thanks Barrie,

    John
    John,

    Care to provide a small sample of your data along with expected results?

    Aladin

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Barrie, I also found this that might be useful to you.

    http://www.rb-ad.dircon.co.uk/rob/ex...lumnletter.htm

    Using that helped solve my column problems


  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    I can. Let me find an sheet that had a great example. brb

  7. #7
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    John, you've got the right man on the job now. I'm curious to see what Aladin comes up with.

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok Aladin, Here is a sample...I sorted Division, Age, & category for easy viewing, but those columns will be sorted after ranking the scores according to groups. I hope this helps. As you can see each rank is isolated within its Division, Age, and Category group. Thank you very much..john

    Score Rank Division Age Category
    95 1 1 20-25 A
    50 2 1 20-25 A
    72 1 1 20-25 B
    60 2 1 20-25 B
    35 3 1 20-25 B
    89 1 1 20-25 C
    78 1 2 26-30 C
    56 2 2 26-30 C
    23 3 2 26-30 C
    87 1 2 26-30 D
    55 2 2 26-30 D
    54 3 2 26-30 D
    97 1 3 20-25 E
    94 2 3 20-25 E
    90 3 3 20-25 E
    76 1 3 20-25 F
    74 2 3 20-25 F
    88 1 3 20-25 G
    45 1 4 26-30 G
    30 2 4 26-30 G
    20 3 4 26-30 G
    66 1 4 26-30 H
    55 2 4 26-30 H
    44 3 4 26-30 H

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-03-08 14:54, JohnJay wrote:
    Ok Aladin, Here is a sample...I sorted Division, Age, & category for easy viewing, but those columns will be sorted after ranking the scores according to groups. I hope this helps. As you can see each rank is isolated within its Division, Age, and Category group. Thank you very much..john

    Score Rank Division Age Category
    95 1 1 20-25 A
    50 2 1 20-25 A
    72 1 1 20-25 B
    60 2 1 20-25 B
    35 3 1 20-25 B
    89 1 1 20-25 C
    78 1 2 26-30 C
    56 2 2 26-30 C
    23 3 2 26-30 C
    87 1 2 26-30 D
    55 2 2 26-30 D
    54 3 2 26-30 D
    97 1 3 20-25 E
    94 2 3 20-25 E
    90 3 3 20-25 E
    76 1 3 20-25 F
    74 2 3 20-25 F
    88 1 3 20-25 G
    45 1 4 26-30 G
    30 2 4 26-30 G
    20 3 4 26-30 G
    66 1 4 26-30 H
    55 2 4 26-30 H
    44 3 4 26-30 H
    John,

    I'll assume that A1:E25 houses the sample data you provided.

    B is the column whre you want the ranking determined by category, divison, and age.

    In B2 array-enter and copy down as far as needed:

    =MATCH(A2,LARGE(IF(($C$2:$C$25=C2)*($D$2:$D$25=D2)*($E$2:$E$25=E2),$A$2:$A$25),ROW(INDIRECT("1:"&COUNTIF($D$2:$D$25,D2)))),0)

    Note 1. This formula is an expanded version of the "RankIf" formula I devised regarding a similar question by Richard Larocque at the old board. If you have many of rows data, you'll experience a slowdown of your spreadsheet. In that case, you could resort to an alternative approach where space is traded off against time. I also formulated such an alternative again by Larocque's request. You could search the more 2 or 3 more recent archives to see how that alternative looks.

    Note 2. The formula expects that the data are sorted by category, division, and age, the first being the most important.

    Note 3. You need, just to be complete, to hit control+shift+enter at the same, not just enter, to enter an array-formula.

    Aladin

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =MATCH(A2,LARGE(IF(($C$2:$C$25=C2)*($D$2:$D$25=D2)*($E$2:$E$25=E2),$A$2:$A$25),ROW(INDIRECT("1:"&COUNTIF($D$2:$D$25,D2)))),0)
    I'd recommend a minor reformulation:

    {=MATCH(A2,LARGE(IF(($C$2:$C$25=C2)*($D$2:$D$25=D2)*($E$2:$E$25=E2),$A$2:$A$25),ROW($1:$25)),0)}

    which replaces,

    ...ROW(INDIRECT("1:"&COUNTIF($D$2:$D$25,D2)))...

    with,

    ...ROW($1:$25)...

    because:

    1. COUNTIFs are very sluggish
    2. LARGE always places #NUM! errors at end of its resultant array; therefore, MATCH will never seem them.

    Note: that the ROW function argument in this reformulation must always include the 1st row of the worksheet.

    [ This Message was edited by: Mark W. on 2002-03-08 16:20 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •