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>
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Ragnar1211

Well-known Member
Joined
Jul 10, 2008
Messages
571
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)
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Change your formula to
=RANK(B2,$B$2:$B$6,0)-1

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

tcrapo

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

Watch MrExcel Video

Forum statistics

Threads
1,108,706
Messages
5,524,418
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top