Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

Without using VBA

share|improve this question
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


share|improve this answer
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


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

share|improve this answer

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):

share|improve this answer

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .