Need tie breaker for ranking

**BuzzT** · 02-08-2011, 12:35 PM

The attached spreadsheet (which has a bunch of hidden columns) is being used to generate points based on ranking and then at the end of the 4 weeks, give a ranking based on the 4 weeks' totals.

The only problem I have right now, is that if there is a tie in the final point score (column V) the rankings (coulmn W) are assigned sequentially in the order of the rows on the spreadsheet (i.e. if Name 3 and Name 4 were tied for third place, Name 3 would be third and Name 4 would be fourth).

What I need is to use column Y as the tie breaker. So, if Name 3 had 16 in column Y and Name 4 had 17, Name 4 would in fact be in thrid place, Name 3 would be in fourth, and then the ranking would continue with 5th and 6th places being assigned sequentially.

I have tried making changes in Column W, but even after many combinations I have still had no success thus far.

Any ideas?

**DonkeyOte** · 02-08-2011, 12:39 PM

Perhaps:

W3:
=1+SUMPRODUCT(($V$3:$V$8+$Y$3:$Y$8/1000>$V3+$Y3/1000)+0)
copied down to W8

of course you might still want to add a unique identifier to the above (ROW) to account for multiple instances of V & Y being identical.

**BuzzT** · 02-08-2011, 12:54 PM

Holy...! How did you come up with that so fast? This is almost there, but what I did notice is that in the (unlikely) event, that there is also a tie in column Y, I have 2 identical rankings, which will then break my vlookup a litte further down. Is there a way to have a combination of what I had previously and this? So, if there was a tie, go to the tie breaker, and if there is also a tie there, rank sequentially?

I know it's a lot, but it would be perfect if I could do that...

**DonkeyOte** · 02-08-2011, 01:04 PM

If I've understood - in terms of precedence:

Rank by V (big before small)
Where V Match Rank by Y (big before small)
Where V & Y match Rank by Row number (small > big)

=1+SUMPRODUCT(($V$3:$V$8+$Y$3:$Y$8/1000-MOD(ROW($V$3:$V$8)-ROW($V$3)+1,-(ROWS($V$3:$V$8)+1))/1000000>$V3+$Y3/1000-MOD(ROWS($V$3:$V3),-(ROWS($V$3:$V$8)+1))/1000000)+0)

edited: missing some parentheses

**DonkeyOte** · 02-08-2011, 01:10 PM

edit: this version is probably a little simpler:

W3:
=1+SUMPRODUCT(($V$3:$V$8*10^5+$Y$3:$Y$8+1-ROW($V$3:$V$8)/1000>$V3*10^5+$Y3+1-ROW($V3)/1000)+0)
copied down

creates a unique number for each row: Vn * 10^5 + Yn + 1-Row(n)/1000 ... use of 1-Row/1000 is so that the lower the row the bigger the decimal

**ChemistB** · 02-08-2011, 12:58 PM

What I would do,
Insert a column W
W3 (dragged down) = V3*1000 + Z3
Then RANK on column W in X3
=RANK(W3,$W$3:$W$8) dragged down
Does that work for you? Not sure why you used such a complex RANKing formula?

RANK will rank ties (in case V and Z are tied) with the same number and then skip a number (common in tournaments, 1st, 2nd, 2nd, 4th for example)
It can be modified to give discrete values