Ranking using data from 2 colums

[jrh]

looking for a ranking formula or variation to use in column D that will rank based on the scores in column B but also account for a major penalty listed by an "x" in column C. Scores with an "x" associated may not score higher than those that don't. Below is a brief example, based on this Entry #5 should be listed as 5th place and Entry #3 should be listed 6th:



Entry #1
Score
Major Penalty
Place






1
57.000
x
7


2
47.000
x
8


3
59.000
x
5


4
72.500

1


5
57.500

6


6
69.500

2


7
62.500

4


8
65.500

3

[Mikey]

Try using this formula in D2 copied down

=COUNTIFS(C$2:C$9,C2&"",B$2:B$9,">"&B2)+IF(C2="x",COUNTIF(C$2:C$9,"<>x"))+1

[jrh]

Mikey,
Thanks for the reply, still not getting the proper results. Created a work around using hidden cells thats working out. Thanks again for the effort.

[TheAntiGates]

That COUNTIFS didn't work for me either. You may not need it now but FWIW I had column F hidden, or off to the side and
D2=RANK(F2,$F$2:$F$9)
F2=IF(LEN(C2)>0,-100,0)+B2
and copy down.

(I assume you have X or not in column C)

[Mikey]

......still not getting the proper results.....

Doesn't it give the correct results for your example? - see yellow cells here (https://www.dropbox.com/s/8d9mqn6qxxwbf8e/Ranking2.xlsx?dl=0)

If you can show some examples where that formula doesn't give correct results I can tweak it as required