How to break ties in the RANK function

Originally Posted by RichardSchollar

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

Originally Posted by RichardSchollar

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

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?

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