proceeding cell formula

[ENEMALI]

i want to rank some students 100 in number in this format(1st,2nd,3rd...........100th) empty cell should not be ranked
this is my question
i need a formula referring to the cell preceding the cell where the formula Will be placed.( regardless of any cell reference) i.e if the scores are in column A, the rank should appear in column B
or
if scores are in column G then ranks must be in H
if possible, someone should help me out please

 

[Oaktree]

Try:

=IF(A1="","",RANK(A1,A$1:A$100))

if you're OK with the rank being 10

Or, referencing http://www.cpearson.com/excel/ordinal.htm, try

=IF(A1="","",RANK(A1,A$1:A$100))&IF(AND(MOD(IF(A1="","",RANK(A1,A$1:A$100)),100)>=10,MOD(IF(A1="","",RANK(A1,A$1:A$100)),100)<=14),"th",CHOOSE(MOD(IF(A1="","",RANK(A1,A$1:A$100)),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

if you want the rank to show 10th with the ordinal suffix.

Enter this in column B... if you then copy from column B to column H, the formula will change to reference column G.

 

[T. Valko]

i want to rank some students 100 in number in this format(1st,2nd,3rd...........100th) empty cell should not be ranked
this is my question
i need a formula referring to the cell preceding the cell where the formula Will be placed.( regardless of any cell reference) i.e if the scores are in column A, the rank should appear in column B
or
if scores are in column G then ranks must be in H
if possible, someone should help me out please

For ordinal ranks...

Book1
	A	B
2	94	1st
3	92	2nd
4	88	3rd
5	85	4th
6	82	5th
7
8	65	6th
9	65	6th
10	59	8th
11	54	9th
12
13	40	10th
14	24	11th
15	18	12th
Sheet1

This formula entered in B2 and copied down:

=IF(A2="","",RANK(A2,A$2:A$15)&MID("thstndrdth",MIN(9,2*RIGHT(RANK(A2,A$2:A$15))*(MOD(RANK(A2,A$2:A$15)-11,100)>2)+1),2))

 

[ENEMALI]

thanks tvalko and oaktree i really appreciate your reply

 

[T. Valko]

thanks tvalko and oaktree i really appreciate your reply

You're welcome. Thanks for the feedback!