# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  How to break ties in the RANK function

## ducecoop

Excel 2003
I am using the RANK function to rank the data in several rows. There are some times ties in the result.
I want to break that tie by taking the greatest value in a different cell in that row
Greatest value wins the tie.
same situation if there are multiple ties (4 "RANK"'s of position 2)

Any help here will be appreciated.

Thanks in advance  
dave

----------


## Bob Phillips

Here is an example

=RANK(B1,$B$1:$B$10,1)+COUNTIF($B$1:$B1,B1)-1

----------


## ducecoop

> Here is an example
> 
> =RANK(B1,$B$1:$B$10,1)+COUNTIF($B$1:$B1,B1)-1



Thanks for the reply but I do not understand how to use it.

my Rank function in from column A in cell A5



```
Please Login or Register  to view this content.
```


Tie breaker is the greatest value in column C

In the attached example

there is a tie between row 5 and row 8
row 5 should win

also there is a tie between row 7 and row 9

row 9 should win

hope that helps

----------


## Gearcutter

Hi, I have put 3 helper columns in at the right of you data  to combine the rankings befor inserting the finalranking in ColA.
have alook at the attachment
 Regards Howard

----------


## Richard Schollar

Does this give you the order you want (eg in an adjacent row 2 cell copied down):

=RANK(B3,$B$3:$B$9,1)+SUMPRODUCT(--($B$3:$B$9=B3),--($C$3:$C$9>C3))

Richard

----------


## ducecoop

> Does this give you the order you want (eg in an adjacent row 2 cell copied down):
> 
> =RANK(B3,$B$3:$B$9,1)+SUMPRODUCT(--($B$3:$B$9=B3),--($C$3:$C$9>C3))
> 
> Richard



 Seems to work.

Can you explain what it is doing so I can duplicate it please

Thanks much

----------


## Richard Schollar

Sure - it uses the rank function to determine an initial rank and then the Sumproduct part of the formula separates out any ranking ties by adding on the number of values in the column where the score is tied (ie ($B$3:$B$9=B3)) *and* the value in the C column is greater than the current C column value ($C$3:$C$9>C3) - so this will give a higher rank to tied values where the C column has the highest value.

----------


## ducecoop

> Sure - it uses the rank function to determine an initial rank and then the Sumproduct part of the formula separates out any ranking ties by adding on the number of values in the column where the score is tied (ie ($B$3:$B$9=B3)) *and* the value in the C column is greater than the current C column value ($C$3:$C$9>C3) - so this will give a higher rank to tied values where the C column has the highest value.



Got it
Thanks much

----------


## profdean

I have a column of unordered ranks (including ties) in 1st column and want to create 2nd column that is an ordered version of the 1st column.  I want to do this with Excel functions (I have Excel 2010, but a function that works for Excel 2007 would be OK too), not the Data Sort through the ribbon.

Col 1
11
10
2
7
3
5.5
13
12
8
15
4
9
5.5
16
14
1

Can someone help?

----------


## oeldere

@profdean
@nmginn

please start your on threat.

it's not allowed to ask an own question in anotherone's treat.

----------


## samktlim

hi richard
chanced upon your post while looking for a solution to my ranking with multiple criteria in excel 2003 and your formula does work for my problem, thanks a million where ever and who ever you are, cheers.....

----------

