Ranking in Excel

  • Thread starter Thread starter Celticshadow
  • Start date Start date
C

Celticshadow

Hi All

I need to rank numbers entered in column A in ascending order while
recognising ties but not skipping ranking numbers. The numbers can range from
1 to 1,000 but there are only 40 rows of data in column A.

Any help would be much appreciated.

Kind Regards

Celticshadow
 
To do this with worksheet functions, you need to sort your list in your desired (Ascending) order.

If your numbers start in A2, then in B2, enter a 1, and in B3, enter the formula

=IF(A3=A2,B2,B2+1)

and copy down.

IF your numbers cannot be sorted, then you would either need another helper column or VBA.
 
Hi Bernie

I require the sorting/ranking to be done automatically as this will
eventually be applied to several columns (but just one for now).
Unfortunately I am not really up to speed with helper columns or VBA.

Kind Regards

Celticshadow
 
Celticshadow,

For example, if your numbers start in A2, enter this in B2:

=RANK(A2,A:A,0)

and enter this in C2:

=IF(ISERROR(MATCH(ROW(A1),B:B,FALSE)),C1,INDEX(A:A,MATCH(ROW(A1),B:B,FALSE)))

Then copy B2:C2, and paste in B3:C??? (matching your data in column A)

HTH,
Bernie
MS Excel MVP
 
Forgot the last bit:

Then apply the technique from my first post, with the formula changed to this in D3

=IF(C3=C2,D2,D2+1)

and with a 1 in cell D2

HTH,
Bernie
MS Excel MVP
 
Can you post a small sample that includes ties and show us what results you
expect.
 
Back
Top