Forum Discussion
Excel rank.eq
I have Excel 365 proplus installed.
I am using the rank.eq function as I am trying to rank numbers with ties .
See below where I use the Rank.eq function - but it does not seem to distinguish between ties butthe old rank() method with countif() correction with ties.
Should rank.eq() work like this ?
| a1 | B | C | D | |||
| 2 | Data | Rank.eq | Rank.eq formula | Old Method - Rank with Countif | Old formula | |
| 3 | 1 | 1 | RANK.EQ(B3,$B$3:$B$7,1) | 1 | RANK(B3,$B$3:$B$7,1)+COUNTIF($B3:B$7,B3)-1 | |
| 4 | 2 | 2 | RANK.EQ(B4,$B$3:$B$7,1) | 2 | RANK(B4,$B$3:$B$7,1)+COUNTIF($B4:B$7,B4)-1 | |
| 5 | 3 | 3 | RANK.EQ(B5,$B$3:$B$7,1) | 4 | RANK(B5,$B$3:$B$7,1)+COUNTIF($B5:B$7,B5)-1 | |
| 6 | 3 | 3 | RANK.EQ(B6,$B$3:$B$7,1) | 3 | RANK(B6,$B$3:$B$7,1)+COUNTIF($B6:B$7,B6)-1 | |
| 7 | 4 | 5 | RANK.EQ(B7,$B$3:$B$7,1) | 5 | RANK(B7,$B$3:$B$7,1)+COUNTIF($B7:B$7,B7)-1 |
3 Replies
paudie190 I believe that both RANK.EQ and RANK do give the correct result. Both take into account ties and gives the same rank, as it should. It seems that what you are trying to achieve with the COUNTIF correction can also be achieved by just sorting (i.e. not ranking) your data and adding a sequential number to each row. Please see the attached workbook for an example of what I mean.
Thanks for your reply - sorting and row/column number would work - but my actual real model ( I just provided a very simple idea of the issue) will not allow sorting of the data
Thanks again
Padraig
- Apologies - Read Rank.eq() functionality more thoroughly and it is not intended to do what I want - it only gives the lower ranking to a set of ties and assigns the next rank number rather than skipping a number. Rank() skips a rank number.
