Rank where Rank 1 is Really 2 and 2 is 3 etc

[tcrapo]

Hi All,

I have a competition score recap where the highest scoring group is called the "Sweepstakes Winner" and the 2nd place group is considered 1st place.

I'd like to use the =Rank formula but have the highest score rank be "SS" and then the 2nd highest score be 1st etc.

Sample is below.

Thanks,
-Tony

Excel 2010

	A	B	C	D
1		Scores	Ranks	Desired Result
2	Group A	96	3	2
3	Group B	94	5	4
4	Group C	99	1	SS
5	Group D	97	2	1
6	Group E	95	4	3

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

Sheet1​

Worksheet Formulas Cell Formula C2 =RANK(B2,$B$2:$B$6,0) C3 =RANK(B3,$B$2:$B$6,0) C4 =RANK(B4,$B$2:$B$6,0) C5 =RANK(B5,$B$2:$B$6,0) C6 =RANK(B6,$B$2:$B$6,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Ragnar1211]

This should work:

=if(RANK([COLOR=Blue]B2,$B$2:$B$6,0[/COLOR])=1,"SS",RANK([COLOR=Blue]B2,$B$2:$B$6,0[/COLOR])+1)

 

[Scott Huish]

Change your formula to
=RANK(B2,$B$2:$B$6,0)-1

and custom format the cells as:
[=0]"SS"

 

[tcrapo]

You guys are awesome.
Thanks for the Speedy response Ragnar1211 & Scott Huish!
Ragnar, the formula just needed a -1 instead of a +1 and works great!

Thanks!
-Tony