Ranking Issue

[IdRatherBeCoding]

Hello all,

I'm having a ranking issue that I'd love some help with!

Let's say that I have three columns: Name, Reporting Department, and Salary. I cannot change the current sorting, but I need to rank a list of people by their salaries.

The data is sorted by Reporting Department A-Z then Alphabetical A-Z. I need to reset the rank every time the Reporting Department changes.

How can I make a formula for this?

Thanks in advance for your help!

Update: Just realized I forgot to mention that I have to do this dynamically so new employees can be added in later (without messing up the ranking system).

[tarquinious]

The data is sorted by Reporting Department A-Z then Alphabetical A-Z. I need to reset the rank every time the Reporting Department changes.

OK, so I have Name in column B, Reporting Department in column C and Salary in column D. Column A is for the Rank.

Firstly, sort the data by Reporting Department Alphabetical A-Z, and then Salary Decending Highest to Lowest.

Finally, in cell A2, copy and paste in the following formula and copy it all the way down the column for each value.

Whenever new data is entered, you will need to re-sort the data as above.

Please Login or Register  to view this content.

This will give you a numeric ranking from 1 to n for each employee from the highest salary to the lowest in each department.

[martindwilson]

try this dynamic ranking chose deptartment from dropdown in e1