# Formula to rank all results but two columns results

#### tonywatsonhelp

##### Well-known Member
Hi Everyone,
I was wonder how to do this,

its our end of year staff awards show, we have had quizes etc durring the year and now we have the results,

so i need to work out how to quickly workout the order everyone has come,

its quite simple i just dont knoe how to do it,
its like this

16 names two columns for results
Score and rating

so highest score wins, down to lowest score,
but when two or more get the same score we use rating to decide who wins,
so all i need is a formula to give me the order everyone has come in?

the table bellow explains

NameResultScoreIf i use RANK i get something like thisNumber? (this is what im trying to get
Bob1112024
sue2221567
Tom33324so 24 is highest11
Sally2322023
Jonh7572022
Jim1211755
Jem2341566
Len551010 is lowest88
so i cant have 3 = "2" and 2="6" so we need to then rank these by the higest Result like above

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### jimrward

##### Well-known Member
One way is create a helper column which is score + (rating/1000) and rank on that new column
so you get 24.333, 20.757, 20.232, 20.111 etc

#### tonywatsonhelp

##### Well-known Member
Excellent Idea,
new there would be a simple one i just could not think of it,
Thanks very much Jim

#### jimrward

##### Well-known Member
It’s just one solution
if you have a second tie breaker you can also add it in but you need to divide that by a higher number
score + (tb1/1000) + (tb2/1000000)
I only chose 1000 because your numbers were high, it get a little bit trickier if one of the tie breaks is lowest value then you might have to look at ((1000-tb3)/1000) etc

Replies
12
Views
742
Replies
4
Views
421
Replies
2
Views
1K
Replies
0
Views
84
Replies
1
Views
54

1,128,017
Messages
5,628,155
Members
416,296
Latest member
smartua

### 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