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.

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.

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.