How to perform ranking based on several conditions?

Student Total   Result  GPC
A   398 PASS    1.2
B   341 PASS    1.6
C   396 PASS    1.8
D   402 FAIL    1.6

Let's say I have a table as above and I would like to perform sorting/ra开发者_如何学Pythonnking based on the following conditions:

1. Sort by "Result" descending
2. Then sort by "GPC" ascending
3. Finally sort by "Total" descending

Hence the end result would be like this:

Student Ranking
A   1
B   2
C   3
D   4

How can I do the sorting above in excel? I had tried to use RANK in the formula but it can only cater to one condition.

You can add a number of intermediate columns to calculate a rank order number based on your sort rules

Results

Formulas

Method Column G: convert PASS/FAIL to a number (other functions require numeric data)
Column H..J: sort the data, a column for each of Total, Result, GPC
Column L..N: Rank the individual results Column O: Combine individual rankings into single value for final ranking

EDIT

Here's as smaller version of the same method

You've pretty much answered your own, very simple, question. Using Excel's Sort dialog (Data>Sort) do the following:

1. Sort by "Result" descending
2. Then sort by "GPC" ascending
3. Finally sort by "Total" descending

You now have a sorted list. Enter the numbers 1 through whatever starting in row 2 in the column of your choice and your list will be ranked. Based on your question I can't see it being more complicated than that.

I have found the solution from the hint above.

Here what I have done:

1- Insert new column next to the "TOTAL" column.
2- Use IF command for fail students i.e IF("rank"="F",0,value of "Total")
3- Now sort your sheet with adding levels according to your need.
4- Carry on with RANK or IF formula, which ever suits your requirement.