Apr 18 2020 12:22 AM
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 |
Apr 18 2020 01:00 AM
Apr 18 2020 01:04 AM
@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.
Apr 19 2020 12:40 AM
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