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

tcrapo

New Member
Joined
Apr 8, 2014
Messages
19
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</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)

<thead>
</thead><tbody>
</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)
 
Upvote 0
Change your formula to
=RANK(B2,$B$2:$B$6,0)-1

and custom format the cells as:
[=0]"SS"
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top