Ranking "Sorta" Sequentially

[Susan McNeill]

I have a list with 26 individuals with a points column. I
want to rank this list with people getting the same score
to get the same rank. However, with the RANK function, if
I have three people with a score of 177, they will all be
ranked at, say 15, but then the ranking skips 16 and 17
and resumes with number 18.

I want everyone that has the same score to get the same
rank, but I don't want excel to skip numbers.

Any solution to this?

Regards -- SLM

 

[Frank Kabel]

Hi
see:
http://www.xldynamic.com/source/xld.RANK.html
and
http://www.cpearson.com/excel/rank.htm

 

[Guest]

if you can sort the list by points, do this instead of
using RANK():
For a sheet with points starting in B1, set
up "ranking" column C. Type 1 in C1. In C2 type =if
(b2=b1,c1,c1+1). Drag this down.

If you can't sort the data for some reason (want name
order?), you might try echoing the points in a separate
data field, sort those and apply the algorithm above to
that set. Then your visible "ranking column" would be a
lookup() expression to compare the visible point value
with the separate list and pull the computed rank out that
way.

hope this helps.

 

[Myrna Larson]

Well, your rank minus 1 is supposed to indicate how many people did better
than you; you can't make that interpretation with your definition of RANK.

Nonetheless, here are a couple of options.

Let's say you have headers (required for my 2nd proposed solution) in row 1,
the names are in A2:A27, points in B2:B27, and you want the rank in C2:C27.

If you can sort the list descending by column B, then in C2 type the number 1.
In C3, use this formula: =IF(B3=B2,C2,C2+1) and copy it down through C27.

My other suggestion doesn't require you to sort the list, but you must create
a list of the unique scores in another location on the sheet. Then you rank
each score against that list of unique scores instead of the scores in column
B, as follows:

Select cells B2:B27 only; then use Advanced Filter and check Copy to Another
Location (say K1) and Unique Records Only. That new list will contain only the
unique scores. Let's say there are 15 unique scores, so you'll have a header
in K1 and the unique scores in K2:K16. Then for your RANK formula use in C2
=RANK(B2,$K$2:K$16) and copy it down through C27.

 

[Susan McNeill]

Option WORKED!!! Like a charm...thanks so much! Regards --
SLM

 

[Myrna Larson]

Just curious: which one did you use?

Option WORKED!!! Like a charm...thanks so much! Regards --
SLM

 

[Aladin Akyurek]

See also:

http://tinyurl.com/4ferw