up vote 1 down vote favorite
share [g+] share [fb]

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

Without using VBA

link|improve this question
38% accept rate
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 at 15:58
feedback

4 Answers

up vote 4 down vote

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

link|improve this answer
This solution is simpler than the other suggested solution, though both work in this instance – Raystafarian Feb 8 at 12:54
feedback
up vote 0 down vote

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))
link|improve this answer
feedback
up vote 0 down vote

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

link|improve this answer
feedback
up vote 0 down vote

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.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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