# Excel RANK function with varing RANGE

Is it possible to force Excel to only use populated cells for the RANK function?

Without using VBA

-
Can you post an example where RANK() is returning an unwanted value because of blank/non-numerical cells? I'm not seeing such behavior in my tests. – Excellll Jan 10 '12 at 15:58

Perhaps you can use an `IF` function to "filter out" blanks. For example to rank data in `A2:A10` which might contain blanks, use this formula in B2 copied down

`=IF(A2="","",RANK(A2,A\$2:A\$10))`

-
This solution is simpler than the other suggested solution, though both work in this instance – Raystafarian Feb 8 '12 at 12:54

Excel sure has a lot of functions.. I think

``````=IF(ISERROR(RANK(B12,\$B\$1:\$B\$20)),"",RANK(B12,\$B\$1:\$B\$20))
``````

is the correct formula for that. See Ranking in Excel Tutorial.

-

It would seem that you don't want to see the #N/A. You will have to do error checking. Something like this will work (it's not elegant because it requires excel to run the function twice if it isn't blank):

``````=IF(ISERROR(RANK(B12,\$B\$1:\$B\$20)),"",RANK(B12,\$B\$1:\$B\$20))
``````
-

You could also use: `=IF(A1<>"", RANK(A1, \$A\$1:\$A\$10),"")` The difference between this and the highest ranked answer is this operates on a `true` whereas the other solution relies on a `false` to operate. Neither is necessarily better in this case

-