Rank with Exclusion

Recently I stumbled upon a problem of ranking observations with exclusion, i.e., not including all of them in the rank. In Excel prior to 2010 you would normally use function =RANK(), in all later versions there are also functions =RANK.EQ() and =RANK.AVG(). They differ only in a way of assigning a rank to observations which have the same values. Coming back to rank with exclusion. Normally ranking numbers from 1 to 7 would result in numbers from 1 to 7 (or 7 to 1, depending on sorting), like in the picture below.

Rank using RANK.EQ in Excel 2010.

Rank using RANK.EQ in Excel 2010.

Assume we add one more column with a text EXCLUDE next to the observations we want to exclude from the ranking. It is not possible to use any version of Excel’s rank formula to do this. But we can construct alternative using array formulas.

=IF(D2="EXCLUDE",0,SUMPRODUCT(--($D$2:$D$8<>"EXCLUDE")*($A$2:$A$8>A2))+IF(D2<>"EXCLUDE",1,0))

Remember this is array formula, so paste it into a cell and press CTRL + ALT + ENTER to make it work. It should then appear in the formula bar as a formula enclosed in curly brackets, {=…}. The result of our formula would be like below.

Custom rank formula with exclution.

Custom rank formula with exclution.

And its formula alternative (you can get formula view by pressing CTRL+`, where ` is a sign on the left next to the button with number 1.

Formula version of custom rank with exclusion.

Formula version of custom rank with exclusion.

Leave a Reply