0

I'm wondering if there is a string version of the Excel Small()/ Large()/ Rank() functions, i.e. a function that could look at a list of strings and tell you how they rank.

E.g. in the following example, I have the list on the left and I think I'd like a function that could return the values on the right, or some variant thereof. 

John        4
            1
David       2
            1
Yeme        5
            1
Gretchen    3

Ideally it would handle blank entries somehow, as shown without just totally failing.

Thanks.

Improve this question
1
  • Not sure what you mean here, you want to sort alphabetically? Or sort each name based on the right hand values in order?
    – Austin T French
    Commented Apr 16, 2014 at 15:07

1 Answer 1

Reset to default
1

Here you go:

=IF(ISBLANK(A2),1,COUNTIF($a$2:$a$8,"<"&a2)+2)

This counts how many names are "less than" than this name. Since it can't really compare to blank cells, this forces blanks as rank 1, and offsets everything by 2 (1 for blank, and 1 to start counting at 1 instead of zero).

Improve this answer
2
  • I can see where you're heading with this, and it sort of works, for me your formula ranks "David" and "Gretchen" at the same level for some reason.
    – SSilk
    Commented Apr 17, 2014 at 16:11
  • Couple small errors... I was missing a $, so the range was moving down. 2nd, I suppose it should end at A8, not A7 (but should be adjusted for your data anyway). I've fixed both in answer.
    – Madball73
    Commented Apr 17, 2014 at 16:30

You must log in to answer this question.

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