# How to break a tie in Rank

1. ## How to break a tie in Rank

Hello,

I have a general number that I am ranking, but when there's a tie, I want it to refer to a number in another column to break the tie. Is there a formula to make this happen? My data is below

Col....A.. B... C
Row.TOT. Rk.. Final
1.....99... 1... 750
2.....98... 2... 436
3.....98... 2... 336
4.....97... 4... 532

I want the 98 TOT with the higher "Final" score to be ranked second and the 98 with the lower "Final" score ranked third. Is there a way to do this so it's already built into the original rank formula and only applies it to the scores in column A that are tied?

Thanks!

2. ## Re: How to break a tie in Rank

welcome to the forum. something like:
=RANK(A1,\$A\$1:\$A\$4)+COUNTIFS(\$A\$1:\$A\$4,A1,\$C\$1:\$C\$4,">"&C1)

3. ## Re: How to break a tie in Rank

Originally Posted by benishiryo
welcome to the forum. something like:
=RANK(A1,\$A\$1:\$A\$4)+COUNTIFS(\$A\$1:\$A\$4,A1,\$C\$1:\$C\$4,">"&C1)
That worked great thanks a bunch!

4. ## Re: How to break a tie in Rank

If I wanted to rank the criteria based on a third column how would I incorporate that into the formula you just provided?

If I only wanted to Rank people that contain 'AL' instead of 'NL' how would I do that still incorporating the tiebreak feature you just assisted me with?

EXAMPLE:

Col....A.. B... C........D
Row.TOT. Rk...Final...League
1.....99... 1... 750.....AL
2.....98... 2... 436.....NL
3.....98... 2... 336.....AL
4.....97... 4... 532.....NL

5. ## Re: How to break a tie in Rank

Can anyone help me with the second part of my question? Thanks

6. ## Re: How to break a tie in Rank

Like this??? I'm sure there's a more elegant way of doing this (i.e. without the helper row). Others?

7. ## Re: How to break a tie in Rank

Originally Posted by Glenn Kennedy
Like this??? I'm sure there's a more elegant way of doing this (i.e. without the helper row). Others?
Ya that's it, but like you said, is there a way to do it without the use of the helper row? Thanks

8. ## Re: How to break a tie in Rank

You can always hide the helper row...

9. ## Re: How to break a tie in Rank

Originally Posted by Glenn Kennedy
You can always hide the helper row...
That's true and what I'll end up doing if no one has a better solution. Thanks for your help!

10. ## Re: How to break a tie in Rank

maybe
=IF(F2="al",SUMPRODUCT((\$F\$2:\$F\$7=F2)*(\$B\$2:\$B\$7+\$D\$2:\$D\$7/10000>B2+D2/10000))+1,"")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1