How to use RANK to break multiple ties.

[Brian]

I have a spreadsheet that ranks 55 of our employees on two certain
catagories, the highest rank being and lowest being 55 in both catagories.
The two ranks are added up as points in another column and ranked in the same
manner. A little VBA code is added and the rows are sorted by the total point
catagory, then ranked by that column.
Having 55 employees on this sheet, there are many times when there are
multiple ties. The company wants the tie breaker to be decided on another
catagory, with the lowest in that catagory being ranked highest. How can I
test this? I will explain the whole worksheet and the actions next.

The spreadsheet is started with the 55 employees sorted by store number and
employee number. The columns are filled with data from reports. The result of
each column that is ranked is static ( stays with that row ). Then the rows
are sorted by the by the 'point total'.

Hope this helps in the explanation a little. I read the definition for the
RANK function, but it only has one explanation for a tie breaker, which
describes if there is a two way tie, not multiples.
Thanx for any ideas in advance.
Brian

 

[Jason Morin]

Add the inverse of the third ranking to the sum of the
first 2. For example:

A1: 88 (sum of 1st 2 rankings for employee 1)
A2: 88 (sum of 1st 2 rankings for employee 2)
B1: 12 (third rank for employee 1)
B2: 20 (third rank for employe 2)

C1: = A1+1/B1 = 88.083
C2: = A2+1/B2 = 88.05

Sort descending on column C.

HTH
Jason
Atlanta, GA