I enjoy building simple excel formulas, but this RANK function "beat" me. Any suggestions?

[650nmWolf]

I am working on a kinda fun golf spreadsheet. I have 18 holes going across in columns (hole #1 is A1, #2 is B1, etc). Each hole is either a par 3, par 4, or par 5. Each hole has a handicap rating (or a level of difficulty between 1-18 meaning the hole with a 1 handicap is the most difficult hole). In my spreadsheet, A1 would be 1 (hole #1 ), A2 is 4 (par 4), A3 is 3 (3rd hardest hole on course). So column A looks like this:
Row 1: 1
Row 2: 4
Row 3: 3

Column B looks like this (hole #2 , par 3, 2nd hardest hole on course):
Row 1: 2
Row 2: 3
Row 3: 2

I now want to re-rank the holes, but throw out all the par 3 holes. In my example above, hole #2 is a par 3 so it would not be ranked. There are typically four par 3 holes on a course so my new ranking would eliminate those and the new handicap rankings would be 1-14 (instead of 1-18).

Here is the hard part for me and my question to you guys: If I want to add a 4th row and call it "Handicap w/o par 3's", how do I do that? Please keep in mind that I want to keep my columns lined up. Example: Hole #1 was the 3rd hardest hole, but the new 4th row should show it as being the 2nd hardest hole since hole #2 was removed.

I have tried a few things and maybe gotten close, but it is getting complicated and I still cant get the correct results. Anyone have thoughts on this? Thanks so much for anyone reading this far! This is not a life or death job security issue, but I still dont want to let excel beat me. Ha!

 

[650nmWolf]

Maybe a simpler way to state my situation: 9 Olympians in a race. Russia is disqualified later for doping. What is the formula to determine the "Final Outcome" finishes (in red)?

Jersey #:	1	2	3	4	5	6	7	8	9
Country:	USA	UK	S. Africa	Russia	UK	Swiss	Russia	USA	France
Original Finish:	2	4	1	3	8	5	7	6	9
Final Outcome:	2	3	1	X	6	4	X	5	7

<tbody>
</tbody>

 

[Eric W]

Perhaps:

	A	B	C	D	E	F	G	H	I	J
1	Jersey #:	1	2	3	4	5	6	7	8	9
2	Country:	USA	UK	S. Africa	Russia	UK	Swiss	Russia	USA	France
3	Original Finish:	2	4	1	3	8	5	7	6	9
4	Final Outcome:	2	3	1	X	6	4	X	5	7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula B4 =IF(B2="Russia","X",COUNTIFS($B$2:$J$2,"<>Russia",$B$3:$J$3,"<"&B3)+1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Put the B4 formula in and drag to the right.

 

[650nmWolf]

Eric,
Thank you, thank you, thank you! Very elegant, clean and simple. I appreciate you taking some time to help me here. Have a Happy New Year!

 

[Eric W]

Glad to help!

Happy New Year!