Results 1 to 9 of 9

Thread: Help with ranking

  1. #1
    New Member
    Join Date
    Aug 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with ranking

    Hi I am having troubles trying to get a ranking formula to work for more than 1 student easily. I have managed to get it to work for 1 student fine but then looking for a way to copy the formula easily to the students underneath such as in the picture. Any help would be appreciated.

    For example;

    John Smith 87 2
    98 1
    23 3
    12 4

    The formula I used for John was =RANK.EQ(C3,$C$3:$C$8,0)

    Jill Jones 91
    23
    21
    78

    Is there an easy to transfer the formula down to Jill and a large number of students underneath her if I have a large list of students. I have used the rank formula to do John Smith but can't find an easy way to move it to subsequent students.

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    9,176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with ranking

    can you repeat post using .... between so we know which column is which
    eg
    john smith................87.....................2
    ...............................98.....................1

  3. #3
    New Member
    Join Date
    Aug 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with ranking

    No worries.

    John Smith....course A.....87......1
    John Smith....course B.....65......2
    John Smith....course C.....54......3
    John Smith....course D.....21......5
    John Smith....course E.....32......4

    Jill Jones....course A.....92......1
    Jill Jones....course B.....23......5
    Jill Jones....course C.....65......3
    Jill Jones....course D.....74......2
    Jill Jones....course E.....43......4

    this would continue for around 900 students and need to rank their scores across their own classes. Hope this makes more sense.

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    9,176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with ranking

    col B col G
    john smith 87 2 ###### 1 98 @@@@@ row 6
    98 1 2 87
    23 3 3 23
    12 4 4 12 row 9
    jill jones 34 4 1 77
    43 3 2 76
    77 1 3 43
    76 2 4 34
    I sorted the formulas for the first table
    and copied the helper columns down
    then the formulas from c6:c9
    into c11:c14
    copying b6 to G9 into b11 to g14 is easy
    ######
    =OFFSET(G5,MATCH(B6,G6:G9,0),-1)
    @@@@@
    =LARGE(B6:B9,1)
    the last 1 could be replaced by F6
    the jill jones rankings were calculated
    by the copied down formula

  5. #5
    Board Regular
    Join Date
    Aug 2017
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with ranking

    Put this formula in D1 =rank.eq(c1,indirect("c"&int(row()/6)*6+1&":c"&int(row()/6)*6+5))
    Copy to 4 rows below
    Copy all 5 rows for next student
    Last edited by mart37; Aug 12th, 2017 at 11:09 AM.

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    12,692
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with ranking

    Maybe something like this


    A
    B
    C
    D
    1
    Student
    Course
    Score
    Rank
    2
    John Smith
    course A
    87
    1
    3
    John Smith
    course B
    65
    2
    4
    John Smith
    course C
    54
    3
    5
    John Smith
    course D
    21
    5
    6
    John Smith
    course E
    32
    4
    7
    8
    Jill Jones
    course A
    92
    1
    9
    Jill Jones
    course B
    23
    5
    10
    Jill Jones
    course C
    65
    3
    11
    Jill Jones
    course D
    74
    2
    12
    Jill Jones
    course E
    43
    4


    Formula in D2 copied down
    =IF(A2="","",COUNTIFS(A:A,A2,C:C,">"&C2)+1)

    Hope this helps

    M.

  7. #7
    Board Regular
    Join Date
    Aug 2017
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with ranking

    To simplify copying
    Put in D1 and copy down
    =if(c1="","",rank.eq(c1,indirect("c"&int(row()/6)*6+1&":c"&int(row()/6)*6+5)))

  8. #8
    New Member
    Join Date
    Aug 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with ranking

    Quote Originally Posted by Marcelo Branco View Post
    Maybe something like this


    A
    B
    C
    D
    1
    Student
    Course
    Score
    Rank
    2
    John Smith
    course A
    87
    1
    3
    John Smith
    course B
    65
    2
    4
    John Smith
    course C
    54
    3
    5
    John Smith
    course D
    21
    5
    6
    John Smith
    course E
    32
    4
    7
    8
    Jill Jones
    course A
    92
    1
    9
    Jill Jones
    course B
    23
    5
    10
    Jill Jones
    course C
    65
    3
    11
    Jill Jones
    course D
    74
    2
    12
    Jill Jones
    course E
    43
    4


    Formula in D2 copied down
    =IF(A2="","",COUNTIFS(A:A,A2,C:C,">"&C2)+1)

    Hope this helps

    M.
    Thanks, that worked perfectly..

  9. #9
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    12,692
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with ranking

    You are welcome. Thanks for the feedback.

    M.

User Tag List

Tags for this Thread

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
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com