Excel Ranking tie assistance

Question

Can anyone help me to do the following in relation to ties when using the Excel Rank function?

Col A contains scores and B contains the rank. I am quite happy with this except that I would like to show an '=' next to the ranking where it is a tie:

Score   Rank
66  3
64  4= 
63  6 
68  2
64  4=  
81  1

etc

Many thanks.

Answer 1

You can combine your RANK with COUNITF. Place the following into cell B3 as per the example

=RANK(A3,$A$3:$A$7)&IF(COUNTIF($A$3:$A$7,A3)>1,"=","")

Note, if you are using Excel 2013 or 2016, it would be a good idea to replace RANK with RANK.EQ

Answer 2

This can be done in another column next to the column where you have ranked it.

Step 1: Rank the numbers in a simple manner using RANK.EQ

Output:

(A)|(B)
66 | 3
64 | 4
63 | 6
68 | 2
64 | 4
81 | 1

Step 2: In another column use the code IF(COUNTIF(A:A,A1)>1,CONCATENATE(B1,"="), B1)

Output:

(A)|(B)|(C)
66 | 3 | 3
64 | 4 | 4=
63 | 6 | 6
68 | 2 | 2
64 | 4 | 4=
81 | 1 | 1

You can paste the values and remove the columns as required.

Hope it helps. :)