Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Find Rank and Award accordingly

  1. #1
    New Member
    Join Date
    Aug 2014
    Location
    Canberra
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Find Rank and Award accordingly

    Here we need to award each group as First, Second, Third, Fourth and Fifth according as the total marks they get in 4 exams. Addition of additional columns are not allowed. So, the Result column must contain words First, Second, Third, Fourth and Fifth as per the marks obtained by the respective group.

    Groups 1st Exam 2nd Exam 3rd Exam 4th Exam Result
    A 85 87 45 76
    B 26 45 85 78
    C 85 96 63 62
    D 48 49 85 74
    E 47 74 58 75

    Thanks in advance.

  2. #2
    Board Regular
    Join Date
    Aug 2014
    Location
    Norway
    Posts
    598
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Rank and Award accordingly

    Quote Originally Posted by Robin_ View Post
    Here we need to award each group as First, Second, Third, Fourth and Fifth according as the total marks they get in 4 exams. Addition of additional columns are not allowed. So, the Result column must contain words First, Second, Third, Fourth and Fifth as per the marks obtained by the respective group.

    Groups 1st Exam 2nd Exam 3rd Exam 4th Exam Result
    A 85 87 45 76
    B 26 45 85 78
    C 85 96 63 62
    D 48 49 85 74
    E 47 74 58 75

    Thanks in advance.
    Well, you asked for it, Paste this formula in cell F2, and drag down:


    =IF(RANK(B2;B$2:B$6)=1;"First";IF(RANK(B2;B$2:B$6)=2;"Second";IF(RANK(B2;B$2:B$6)=3;"Third";IF(RANK(B2;B$2:B$6)=4;"Fourth";IF(RANK(B2;B$2:B$6)=5;"Fifth";"")))))&", "& IF(RANK(C2;C$2:C$6)=1;"First";IF(RANK(C2;C$2:C$6)=2;"Second";IF(RANK(C2;C$2:C$6)=3;"Third";IF(RANK(C2;C$2:C$6)=4;"Fourth";IF(RANK(C2;C$2:C$6)=5;"Fifth";"")))))&", "&IF(RANK(D2;D$2:D$6)=1;"First";IF(RANK(D2;D$2:D$6)=2;"Second";IF(RANK(D2;D$2:D$6)=3;"Third";IF(RANK(D2;D$2:D$6)=4;"Fourth";IF(RANK(D2;D$2:D$6)=5;"Fifth";"")))))&", "& IF(RANK(E2;E$2:E$6)=1;"First";IF(RANK(E2;E$2:E$6)=2;"Second";IF(RANK(E2;E$2:E$6)=3;"Third";IF(RANK(E2;E$2:E$6)=4;"Fourth";IF(RANK(E2;E$2:E$6)=5;"Fifth";"")))))

    Will yied the results:
    First, Second, Fifth, Second
    Fifth, Fifth, First, First
    First, First, Third, Fifth
    Third, Fourth, First, Fourth
    Fourth, Third, Fourth, Third


    One issue is when they have the same scores, then they will get the same notation "First", however, "second place" first score after the top 2, will yield third, not Second. This is okay?

    If you are NA, then replace the "dividers" within the formula with "," instead of ";" as I am using
    Last edited by Arithos; Aug 29th, 2014 at 08:09 AM.
    Regards,
    Sturla

    - I try to post my changes in your code in RED
    - If posting VBA code, please use Code Tags


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

    Default Re: Find Rank and Award accordingly

    I think, I became unable to make you understand. I don't need those many Firsts and Seconds.....
    I will make you clear.

    Suppose we have an additional column in between 4th Exam and Result named Total

    Then that total column contains... numbers
    293
    234
    306
    256
    254
    Now, we need to award the First, Second, Third, Fourth and Fifth based on the total. Now, at the end the Result column should look like
    Second
    Fifth
    First
    Third
    Fourth

    If we are allowed to add a column Total like this, then it is not a big deal for me. But, we are not allowed to add any additional column.
    I hope, I made you clear.

    Thank you.

  4. #4
    Board Regular
    Join Date
    Aug 2014
    Location
    Norway
    Posts
    598
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Rank and Award accordingly

    Quote Originally Posted by Robin_ View Post
    I think, I became unable to make you understand. I don't need those many Firsts and Seconds.....
    I will make you clear.

    Suppose we have an additional column in between 4th Exam and Result named Total

    Then that total column contains... numbers
    293
    234
    306
    256
    254
    Now, we need to award the First, Second, Third, Fourth and Fifth based on the total. Now, at the end the Result column should look like
    Second
    Fifth
    First
    Third
    Fourth

    If we are allowed to add a column Total like this, then it is not a big deal for me. But, we are not allowed to add any additional column.
    I hope, I made you clear.

    Thank you.
    Well, you did make it clear now -.-
    Alot easier like this.. Give me a sec
    Last edited by Arithos; Aug 29th, 2014 at 08:36 AM.
    Regards,
    Sturla

    - I try to post my changes in your code in RED
    - If posting VBA code, please use Code Tags


  5. #5
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,473
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Rank and Award accordingly

    Hi,

    Perhaps this, in F2:

    =MATCH(SUM(B2:E2),LARGE(MMULT($B$2:$E$6,TRANSPOSE(N(INDEX(COLUMN($B$2:$E$6),,)))^0),N(INDEX(ROW(INDIRECT("1:"&ROWS($B$2:$E$6))),,))),0)

    Note that ties will be given precisely the same rank. For example, if Groups A and C had the same totals, the results would be, from top to bottom: 1, 5, 1, 3, 4.

    This is one of the standard ranking systems. Please say if you would prefer an alternative system to deal with ties.

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    New Member
    Join Date
    Aug 2014
    Location
    Canberra
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Rank and Award accordingly

    Still unsatisfied.

    I need words than ranked numbers. Looking around the formula, I don't think, I can manage to insert any if condition or any function to convert those numbers into words.

  7. #7
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,473
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Rank and Award accordingly

    Quote Originally Posted by Robin_ View Post
    Still unsatisfied.

    I need words than ranked numbers. Looking around the formula, I don't think, I can manage to insert any if condition or any function to convert those numbers into words.
    Will there only ever be 5 entries to rank? It might well require VBA if not, since there is no decent shorthand method to tell Excel to return e.g. Seventy-First, etc.

    Also, you didn't say whether the tie-break system was satisfactory.

    I might also ask if there can ever be any empty cells within your range, as the formula would need a small adjustment if that were the case.
    Advanced Excel Techniques: http://excelxor.com/

  8. #8
    Board Regular
    Join Date
    Aug 2014
    Location
    Norway
    Posts
    598
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Rank and Award accordingly

    Quote Originally Posted by XOR LX View Post
    Will there only ever be 5 entries to rank? It might well require VBA if not, since there is no decent shorthand method to tell Excel to return e.g. Seventy-First, etc.

    Also, you didn't say whether the tie-break system was satisfactory.

    I might also ask if there can ever be any empty cells within your range, as the formula would need a small adjustment if that were the case.

    He wants it like this:
    A Second
    B Fifth
    C First
    D Third
    E Fourth

    Without a "helping" column


    And I would use VBA for this. Unless he has an assignement that requires him to report a formula

    I use: =CHOOSE(MATCH(RANK(SUM(B2:E2);F$2:F$6);{1;2;3;4;5});"First";"Second";"Third";"Fourth";"Fifth")


    where F is sum of rows, I thought it would be simple to implement SUM() intor rank "ref" area, but it was not.. Do you know how?
    Last edited by Arithos; Aug 29th, 2014 at 09:11 AM.
    Regards,
    Sturla

    - I try to post my changes in your code in RED
    - If posting VBA code, please use Code Tags


  9. #9
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,319
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Find Rank and Award accordingly

    Try this:-
    NB:- The code will match duplicate Totals as equal places, where appropriate.

    Code:
    Sub MG29Aug56
    Dim Rng             As Range
    Dim Dn              As Range
    Dim n               As Integer
    Dim Tot             As Double
    Dim K               As Variant
    Dim oWds            As Variant
    Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    For Each Dn In Rng
        Tot = Application.Sum(Dn.Offset(, 1).Resize(, 4).Value)
            If Not .Exists(Tot) Then
                .Add Tot, Dn
            Else
                Set .Item(Tot) = Union(.Item(Tot), Dn)
            End If
    Next
    oWds = Array("First", "Second", "Third", "Fourth", "Fifth")
    For n = 1 To 5
        If .Item(Application.Large(.keys(), n)) > 1 Then
            For Each Dn In .Item(Application.Large(.keys(), n))
                .Item(Application.Large(.keys(), n)).Offset(, 5) = oWds(n - 1)
            Next Dn
        Else
            .Item(Application.Large(.keys(), n)).Offset(, 5) = oWds(n - 1)
        End If
    Next n
    End With
    End Sub
    Regards Mick

  10. #10
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,473
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Rank and Award accordingly

    Quote Originally Posted by Arithos View Post
    He wants it like this:
    A Second
    B Fifth
    C First
    D Third
    E Fourth

    Without a "helping" column


    And I would use VBA for this. Unless he has an assignement that requires him to report a formula

    I use: =CHOOSE(MATCH(RANK(SUM(B2:E2);F$2:F$6);{1;2;3;4;5});"First";"Second";"Third";"Fourth";"Fifth")


    where F is sum of rows, I thought it would be simple to implement SUM() intor rank "ref" area, but it was not.. Do you know how?
    I know. And my formula can be adapted to give the actual words quite easily, without the need for a helper column.

    I just need to know first if the data is fixed at 5 rows. It's all very well adding in a few text values to a formula, though it would soon become unfeasible if we were looking at ranking up to e.g. "fiftieth".

    Regards
    Advanced Excel Techniques: http://excelxor.com/

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
  •