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.

Originally Posted by mrice

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.