Ranking in Excel based on multiple criteria

Hi,

I'd really appreciate some help in ranking the player scoring data below:

Group Player Total_Points Net_Score Previous_Ranking Main TB-1 TB-2 TB-3

I A 24 2 1 3 3 3 3
I B 29.5 23 2 1 1 1 1
I C 28.5 23 3 2 2 2 2
I D 18 -2 4 6 6 6 6
I E 9.5 -23 5 9 9 9 9
I F 1.5 -25 6 13 13 13 13
I G 20 2 7 4 4 4 4
II H 20 49 8 4 5 5 5
II I 0 -39 10 14 14 14 14
II J 5 -16 11 11 11 11 11
II K 9 -11 12 10 10 10 10
II L 14 16 14 7 7 7 7
II M 14 16 15 7 7 7 8
II N 5 -19 16 11 11 12 12.

The criteria are :

Main - Ranking based on Total_Points ( column C)
Tie-Breaker 1 - If the tie is across players from 2 Groups (column A) , player from Group I wins over the player from Group 2 (e.g. players G and H)
Tie-Breaker 2 - If there is still a tie within the players from 2 groups, the player with the higher Net_Points (column D) wins (e.g. players J and N)
Tie-Breaker 3- If there is still a tie the player with the better ranking from the Previous_Ranking (column E) wins (e.g. players L and M)

The expected result is in column I (TB-3).

Question : Is there a formula or set of formulae in Excel that I can use to automate the ranking process?

Try:

=SUMPRODUCT(--($C$4:$C$17*10^13+($A$4:$A$17="I")*10^9+$D$4:$D$17*10^5-$E$4:$E$17
>C4*10^13+(A4="I")*10^9+D4*10^5-E4))+1

Thanks very much Phuocam. It works fine.