Ranking

[snowy]

Hi,
I'm trying to rank scores dependant, first g, then lowest f, then highest C. I think I've managed this. However I want the ranking to ignore a total score if the competitor is "HC"(see Table A). I can only do this if I don't put the final score in column G.(see Table B).

Table A

A B C D E F G H
1 178 19.1 0.0 10.4 29.5 3
2 132 40.0 0.0 0.0 40.0 6
3 hc 155 29.5 0.0 0.0 29.5 HC
4 167 24.1 0.0 6.0 30.1 4
5 154 30.0 0.0 4.0 34.0 5
6 162 26.4 3.1 0.0 29.5 1

Table B

A B C D E F G H
1 178 19.1 0.0 10.4 29.5 2
2 132 40.0 0.0 0.0 40.0 5
3 hc 155 29.5 0.0 0.0 HC HC
4 167 24.1 0.0 6.0 30.1 3
5 154 30.0 0.0 4.0 34.0 4
6 162 26.4 3.1 0.0 29.5 1

In Table A I used the formula =IF(B2="hc","HC",(RANK.EQ($G2,$G$2:$G$7,1)+SUMPRODUCT(--($G$2:$G$7=$G2),--($F$2:$F$7<$F2))+SUMPRODUCT(--($G$2:$G$7=$G2),--($F$2:$F$7=$F2),--($C$2:$C$7>$C2))))

Any help gratefully received.
Many thanks
S