Ranking based on two criteria

[Mike]

I have a number of different sales projects running. Each
person on a project is given a bonus based on their
ranking within the project, with ranking based on a number
of sales per hour (SPH). The basic information is in a
simple CSV file that I import into Excel via ODBC. Column
A is Name, B is Project, C is # of Sales and D is Hours.

In column E I have the formula =Cx/Dx to get sales per
hour. I can use the RANK() function on column E to rank
all the employees by SPH regardless of project, but I need
to use the project name as well. So I need a formula for
the Rank colum to produce results something like this:

Employee Project SPH Rank
Bob ProjectX 0.15 3
John ProjectX 0.24 1
Bill ProjectX 0.19 2
Fred ProjectY 1.32 3
Gene ProjectY 2.33 1
Mike ProjectY 2.10 2

 

[JMay]

This might help..
http://www.cpearson.com/excel/rank.htm

 

[Peter Atherton]

Mike

Why not try Sorting on more than one criterion?

From the Data Menu click Sort, choose Column 2 or Project
for the first sort, then Column E for the next sort this
will give you what you need. You can use a fill to insert
rank numbers for each project.

If you need to resore the data to its original order enter
a list of number 1 to n in an adjacent column and sort
again on this column to restore the original list.

Regards
Peter