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

#### tcrapo

##### New Member
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
ABCD
1Scores RanksDesired Result
2Group A9632
3Group B9454
4Group C991SS
5Group D9721
6Group E9543

</tbody>
Sheet1

Worksheet Formulas
CellFormula
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)

</tbody>

<tbody>
</tbody>

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
This should work:
Code:
``=if(RANK([COLOR=Blue]B2,\$B\$2:\$B\$6,0[/COLOR])=1,"SS",RANK([COLOR=Blue]B2,\$B\$2:\$B\$6,0[/COLOR])+1)``

=RANK(B2,\$B\$2:\$B\$6,0)-1

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

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

Replies
1
Views
55
Replies
1
Views
184
Replies
11
Views
423
Replies
14
Views
848
Replies
5
Views
354

1,216,107
Messages
6,128,870
Members
449,475
Latest member
Parik11

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back