Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Formula to Rank data

    I have a spreadsheet where I tried to use formulas to rank the students and marks -see section of my spreadsheet A25 to G 29.

    However, where the marks are the same, the formula returns the student name and student number twice

    It would be appreciated if someone could assist me



    Howard
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In addition to your question, you need to use MATCH with the ",0" option no the ",1" to get an exact match, you approximate match with unsorted data is not accurate, the wrong people are being pulled from the list.

    You need a column to rank with no ties. If you use the row as a tie-breaker, In O5 enter: Rank
    In O6:
    =RANK(G7,$G$7:$G$17)+COUNTIF($G$6:$G6,G7)
    Copy O6 down the columns.

    In O25:O29 enter 1 through 5

    In C25:
    =INDEX($A$7:$A$17,MATCH(D25,$D$7:$D$17,0))

    In D25:
    =INDEX($D$7:$D$17,MATCH(O25,$O$7:$O$17,0))

    In G25:
    =LARGE($G$7:$G$19,O25)

    Copy c25:G25 down the columns.

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the help, much appreciated

    Howard

Posting Permissions

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