Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

4 Answers 4

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

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

=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.

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

=IF(ISERROR(RANK(B12,$B$1:$B$20)),"",RANK(B12,$B$1:$B$20))
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.