Rank in Excel

[koty]

I have a markssheet in which I want to use the Rank formula wherein it keeps
updating the rank of each student as I keep adding the students.
I do not want to specify a range of cells for it to compare. It should
automatiocally sense the presense of Data and allocate rank accordingly.

 

[N Harkawat]

=RANK(A1,OFFSET($A$1,0,0,COUNTA($A$1:$A$1000)))
will create a dynamic list that changes with the length of your list

 

[Biff]

Hi!

A slightly different approach.

Assume column B contains the values to rank starting in B2.

Use a defined dynamic named range.

Insert>Name>Define
Name - Grades
Refers to - =OFFSET($B$1,0,0,COUNTA($B:$B),1)

Formula to rank:

=IF(B2=","",RANK(B2,GRADES))

Copy down far enough to allow for future additional
entries to the list.

Biff

 

[Biff]

Ooops! minor edit, left out "

=IF(B2=","",RANK(B2,GRADES))

should be:

=IF(B2="","",RANK(B2,GRADES))

Biff