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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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,958
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,478
Messages
5,831,913
Members
430,091
Latest member
Generally_confused

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
Top