Assigning Multiple Rankings to Data based on different columns

[Mike610]

As part of a project, I'm trying to take a list of 300 names and assign each person rankings based on different sets of criteria which is provided in separate columns on the sheet. I sorted by the first column of interest, created an additional column, and typed the number 1 to the first ranked person based on the sorted criteria. I then used =A2+1 and copied it down to 300 to assign a number to each name.

The problem is that when i try to sort against a second set of criteria, i get an error message, because the formula used to assign the numbers above is no longer valid based on the new sorting.

I resorted to typing in the numbers 1-300 going down a spreadsheet; and am looking for a way to number a sheet going down (in this case from 1 to 300) without having to type in each number manually, and without excel reading it as a formula once it has been done.


Example - I'm using the below 4 categories, and for each individual i want to show their ranking on Test Score 1 2 and 3

Name Test Score 1 TestScore 2 Test Score 3 Rank Test 1 Rank Test2
Alex 95 90 85 1 2
Jane 80 95 90 2 1

[Flintstone]

Mike610:

This might help you out.

http://www.cpearson.com/excel/rank.aspx