+ Reply to Thread
Results 1 to 11 of 11

Ranking Based on Three Criteria

  1. #1
    Registered User
    Join Date
    05-27-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    4

    Ranking Based on Three Criteria

    I've looked at many of the threads and tried all the formulas, but I still can't seem to get this right. Maybe there's something really simple and I'm just missing it, but here's the problem.

    I've four columns (Rank, Name, Score, %) and I've already sorted the data (First by Score, then % (as a tiebreaker), then name (so that it's alphabetical), but now in the first column I need the data ranked automatically.

    I need the final product to look something like this:


    1. Annie 140 80.20
    1. Sarah 140 80.20
    3. Rena 140 79.45
    4. Paul 139 80.20
    5. Cara 138 79.45
    5. Lara 138 79.45
    7. Tara 137 81.90

    So I need the 1,1,3,4,5,5 column where it ranks the data according to score first and then percentage and if there are ties, it leaves them there.

    Am I missing something obvious? Thanks for any help!

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,966

    Re: Ranking Based on Three Criteria

    I would suggest the following ...
    • Create a helper column adjacent to the set of data containing the formula =1000*C1 + D1 (assuming the score is in column C and the percentage in column D. Helper column is column E.
      Use the RANK or RANK.EQ function to rank on the basis of the value in the helper column. e.g. =RANK.EQ(E1,$E$1:$E$7)

    The idea is that by multiplying the first parameter by a big number (1000 in this case) the size of the second number will always be small in comparison and therefore dealt with as a second ranking criterion.
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,749

    Re: Ranking Based on Three Criteria

    I used this, copied down, and it seemed to do just fine?

    =RANK(C2,$C$2:$C$8)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,399

    Re: Ranking Based on Three Criteria

    Hi annie and welcome to the forum,

    See if the attached, that has a helper column helps explain what mrice means.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Ranking Based on Three Criteria

    mrice provides a good suggestion... but I get the impression it "weights" Score too heavily over %. No different than sorting with Score having priority over %. What you really need to establish is the relationship between Score and %, and use that factor for the helper column value... as it appears Score and % are not proportional.

    Also, I don't understand how Rank column wieghs into it all. What is the basis for assigning a rank value? In my mind, after the sorting you are wanting, the rank would be 1,2,3,4,5,etc... but the so-called tied ones would have equal rank, such as 2,2,2 for three persons tied for second.

  6. #6
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Ranking Based on Three Criteria

    Try

    =SUMPRODUCT(--(C2<$C$2:$C$8))+SUMPRODUCT(--($C$2:$C$8=C2),--(D2<$D$2:$D$8))+1
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  7. #7
    Registered User
    Join Date
    05-27-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Ranking Based on Three Criteria

    Quote Originally Posted by mrice View Post
    I would suggest the following ...
    • Create a helper column adjacent to the set of data containing the formula =1000*C1 + D1 (assuming the score is in column C and the percentage in column D. Helper column is column E.
      Use the RANK or RANK.EQ function to rank on the basis of the value in the helper column. e.g. =RANK.EQ(E1,$E$1:$E$7)

    The idea is that by multiplying the first parameter by a big number (1000 in this case) the size of the second number will always be small in comparison and therefore dealt with as a second ranking criterion.
    Thank you, that worked! I had tired the helper column/sum thing, but hadn't multiplied the score which led to it not working as you can have a higher percentage but a lower score.

    I also have another sheet where it's sorted by score first, then percentage, then average overall, and then number of incorrect answers. I do it manually as it's only for about 30 kids, so it's not a big deal right now. But we might start doing it for all of them (500 or so). With the helper column, would I just weigh them all in decreasing numbers so as to get the right ranking? I'll try and see if that works.

    Let me explain the data more. There is an exam with 150 questions. The questions are all worth different points. The most difficult are worth 50 points to the easiest ones that are worth just 5 points. Out of 150 questions, a student gets to pick 30 questions. They can pick the toughies or the easy ones. So we put in their 30 questions they chose to answer and they get the points assigned to that question if they're correct, 0 if they're incorrect. The students are then sorted by their total score for the questions answered correctly, then the percentage of questions they got correct out of total picked, then the overall average of their questions, then the amount of questions they answered incorrectly. The last sort is ascending in that the fewer the amount of incorrect questions, the better it is. Obviously we've added these extra sorts just to make sure they are no ties at all. The last sort has only come into factor a few times, but it does happen.

    I'll try some of the suggestions here and get back to you all. Thank you for such fast responses.
    Last edited by annie82; 05-27-2013 at 06:51 PM.

  8. #8
    Registered User
    Join Date
    05-27-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Ranking Based on Three Criteria

    Quote Originally Posted by jhren View Post
    mrice provides a good suggestion... but I get the impression it "weights" Score too heavily over %. No different than sorting with Score having priority over %. What you really need to establish is the relationship between Score and %, and use that factor for the helper column value... as it appears Score and % are not proportional.

    Also, I don't understand how Rank column wieghs into it all. What is the basis for assigning a rank value? In my mind, after the sorting you are wanting, the rank would be 1,2,3,4,5,etc... but the so-called tied ones would have equal rank, such as 2,2,2 for three persons tied for second.
    I'm not sure I understand the question. The % doesn't come into factor unless they've the same score. So the score definitely needs to be weighted heavily.

    The data is an exam with 150 questions. Each question is worth 10 points and has two parts. If you get both parts correct, you get the full 10 points. If you only get the first part correct, you get 6 points. (You can't get the second part correct w/o getting the first part correct.) If it's incorrect, then you get a 0. So the score is the addition of the 10 pointers, 6 pointers, and the 0s. The percentage is how many questions you got correct out of questions answered (some students forget to answer questions or intentionally leave them blank so as to not hurt their percentage on a question they aren't sure how to answer).

    It's possible for a student to have a 100% by getting a lot of 6 pointers and skipping questions, but it's lower than someone who missed some questions, but has a lot of 10 pointers correct. But if they've the same score, it's better to have answered more questions correctly (because the first part of the question is the more difficult part of the question) and thus they should be ranked higher.
    Last edited by annie82; 05-27-2013 at 06:50 PM.

  9. #9
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Ranking Based on Three Criteria

    Your last two posts seem somewhat contradictory...

    In the former you said the exam has a pool of 150 question ranging in difficulty and awarded points accordingly, 50 for hard, 5 for easy... and the student chooses 30 questions from the pool.

    In the latter you said each question is two-part worth 6 for the first part correct, 10 for both parts correct, 0 if both parts incorrect as second part is dependent on getting first part correct.

    What happened to the pool of questions worth from 5 to 50 points???

    Scoring and percentage of correct answers seem fairly straight forward for the latter. But the former has a built-in difficulty factor. Say two students get the same points score. One chose harder questions, got a lower percentage correct, but ended up with the same score as a student that chose easier questions and got a higher percentage correct. As I understand your ranking system, you are rewarding the student that chose the easier questions, and disregarding the difficulty factor for the student that chose the harder-to-get-correct questions.

    Seems like you need to have total point value of questions in order to weight the score versus percentage correctly. For the sake of discussion let's say student H chose harder questions, got 40% correct with a score of 150 out of a total possible score of 300. Student E chose easier questions, got a score of 150 also, 70% correct out of a total possible score of 180. I haven't evaluated the fairness entirely, but let's try this: 300/150*50%=1, and 180/150*70%=.84. Even though student E got the same score, student H is rewarded for choosing harder questions.

  10. #10
    Registered User
    Join Date
    05-27-2013
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Ranking Based on Three Criteria

    Quote Originally Posted by jhren View Post
    Your last two posts seem somewhat contradictory...
    They are two different exams. One is a placement exam only given to 30 students. The 6/10 points one is given to the entire student body.

    And the risk and reward are built into the questions. That isn't the problem. Some of the easy questions, for example, are actually more time consuming than the harder ones. It is about choosing the questions that are right for yourself.

  11. #11
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Ranking Based on Three Criteria

    Quote Originally Posted by annie82 View Post
    They are two different exams. One is a placement exam only given to 30 students. The 6/10 points one is given to the entire student body.
    So do the students taking the placement exam take both, or just the placement exam? If the latter, it seems to me you can't rank everyone together fairly using the same ranking system.. without weighting accordingly. I can't say what a fair ranking would be, not knowing the questions. If the former, I'd say you can only rank students by the test taken. Lumping scores together definitely doesn't seem fair to me if scoring methods are totally different.

    Quote Originally Posted by annie82 View Post
    And the risk and reward are built into the questions. That isn't the problem. Some of the easy questions, for example, are actually more time consuming than the harder ones. It is about choosing the questions that are right for yourself.
    Who is making the determination the easier ones are more time consuming... someone that knows all the answers before making the assessment??? Is all this explained to the student before they choose their questions? Ultimately, if you stick with ranking the placement exam as is, you are penalizing the person that challenges themself, especially if the ranking system isn't explained in detail before they choose their questions. Explanation should note the objective is to get both the most correct answers AND score the highest points.

    Anyway, I'm not saying that wieghting should be done by my example. Just saying ranking as noted is unfairly wieghted IMO. I leave it to your conscience to determine whether my opinion has any weight

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1