Excel 2007 : Rank Order Question

[pantsarella]

Hi! I have a question about how to rank order some entries in Excel.

I have a spreadsheet with student detention data in it. Each row is an entry which marks the date and reason for why a student received detention. Some of the students have multiple entries, since students received detention more than once throughout the year. I also have student ids in the file, so I can identify those students with multiple entries accurately.

I want to be able to rank order the incidents by student. So if I have this:

Student ID Date of Detention
123 1/1/2011
123 1/2/2011
123 5/5/2011
234 8/15/2011
234 12/11/2011

I want to order it like this:
Student ID Date of Detention Rank Order
123 1/1/2011 1
123 1/2/2011 2
123 5/5/2011 3
234 8/15/2011 1
234 12/11/2011 2

Any thoughts on how to do this? Thanks so much for any guidance you can give!

[DGagnon]

try this formula, assuming your data starts A1-A5

=RANK(A1,A$1:A$5,1)+SUMPRODUCT((A$1:A$5=A1)*(B$1:B$5<B1))

[DGagnon]

on second read of your data, i think you are looking to sort each student, in that case, try this:

=SUMPRODUCT((A$2:A$6=A2)*(B$2:B$6<B2))+1

[pantsarella]

Thanks so much! That second formula worked perfectly!

[DGagnon]

no problem at all, glad i could assist.

could you please do me the favour of marking this thread as [SOLVED]?