Ranking in Excel

[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

 

[Bernie Deitrick]

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.

 

[Celticshadow]

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

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

 

[T. Valko]

Can you post a small sample that includes ties and show us what results you
expect.

 

[Herbert Seidenberg]

Excel 2007
Ten Variations on a Rank theme.
http://www.mediafire.com/file/igmfuzgme2i/04_08_09.xlsx

 

[Herbert Seidenberg]

Excel 2007
Added Step Chart
http://www.mediafire.com/file/tky0j3nchgm/04_11_09.xlsx

 

[Bernd P]

Hello,

If I understand the problem correctly, a simple COUNTIF + COUNTIF
approach would suffice (which works for numbers as well as for texts):
http://www.sulprobil.com/html/sorting.html

Regards,
Bernd