Formula to rank all results but two columns results

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,727
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
 
Solution

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,727
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Excellent Idea,
new there would be a simple one i just could not think of it,
Thanks very much Jim
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
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