|I think that to manage ties with an alphabetical list of the names is going to require a macro.|
However, if you are willing to settle for a non-alphabetical list if there is a tie, then this formula based solution should work:
To start with, I would limit the chances for a tie by replacing the QUOTIENT function with the actual division e.g =D3/C3. You can still display the results in integer format by not showing the decimals, but there would be much less chance of a tie.
You can further ensure against a tie by adding "an offset" to the end of each formula:
This will change the value of the number in the cell just enough to eliminate any ties, but probably not impact the actual results.
Once that's done, You can use LARGE to list the Productivity numbers in descending order:
To avoid having to manually type in the 1, 2, 3, etc. you can use:
=LARGE($B$3:$B$13,ROW()) and drag the formula down.
If you start in Row 1, ROW will increment each time and return 1, 2, 3, etc. If you start in another, e.g. Row 3, use:
ROW()-2 will evaluate to 1, 2, 3 as you drag it down.
OK, now that you have your Productivity numbers in descending order, we need the names. Normally, VLOOKUP is used to look up a value in a table and return a value from a specific column within that table. Unfortuately, VLOOKUP only looks to the right and we need to look left to pick up the name.
Let's say you put your LARGE function starting in F1. In E1, you could use this:
The MATCH portion will match the value in F1 with the same value in B1:B13 and return it's postion, e.g. 5.
The INDEX function will then use that value as the row_num and return the value in e.g. A5, which should be the name of the person associated with that Productivity number.
Give that a try.
Edited 2/21/10 to fix function errors