Ranking Based on Three Criteria

Originally Posted by jhren

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.