Ranking with 3-way tie breaker

[BuzzT]

If you refer to the attached spreadsheet, you'll notice that the first two rows are identical except for 1 column where there is a difference of 0.5 (in favor of the second row), yet the first row is still ranked higher (column R). I can't seem to figure out why. Can anyone offer a suggestion?

FYI, in some of the columns a lower score is actually a better result, but the problem doesn't seem to be as a result of that.

Thank you in advance for any advice.

[6StringJazzer]

Bonjour!

I cannot for the life of me figure out what you are trying to do with the formulas in column R. It would be very helpful if you explained that.

Meantime, you will see that ROW($Qn) is part of your formula where n is the row containing the formula. That is the only thing that could explain a difference between the result of the formula in row 1 vs. row 2, because the formula doesn't reference data in column O. But because I don't know what you are expecting the formula to do, I can't tell you whether that is the correct result.

=1+SUMPRODUCT(($Q$1:$Q$4*10^5+$E$1:$E$4+1+$J$1:$J$4+1+$M$1:$M$4+1-ROW($Q$1:$Q$4)/1000>$Q1*10^5+$E1+1+$J1+1+$M1+1-ROW($Q1)/1000)+0)

[BuzzT]

Sorry... I uploaded another version I had been working with previously. Here is the correct one.

What I am trying to do, is when there is a tie in column S, is have a tie breaker based on the results from columns L, O, and Q.

To test, the values in rows 1 and 2 are the same except in column Q. There is a 0.1% difference between these in favor of row 2, yet for some reason they are both ranked as 1 (column T).

If you change the value from 99.1 to 99.2 in column Q of row 2, it seems to work, so I am stumped as to why it is not 100% accurate.

[6StringJazzer]

What I am trying to do, is when there is a tie in column S, is have a tie breaker based on the results from columns L, O, and Q.

The problem I am having is I don't know what you mean by "tie." The formula is rather tangled and I can't relate it to the goal you are trying to achieve.

In particular the things I can't understand are why you divide row numbers by 1000 and compare the result to the value in S times 100,000:

=1+SUMPRODUCT(($S$1:$S$4*10^5+$L$1:$L$4+1+$O$1:$O$4+1+$Q$1:$Q$4+1-ROW($S$1:$S$4)/1000>$S1*10^5+$L1+1+$O1+1+$Q1+1-ROW($S1)/1000)+0)

and similar for dividing the current row number by 1000:

=1+SUMPRODUCT(($S$1:$S$4*10^5+$L$1:$L$4+1+$O$1:$O$4+1+$Q$1:$Q$4+1-ROW($S$1:$S$4)/1000>$S1*10^5+$L1+1+$O1+1+$Q1+1-ROW($S1)/1000)+0)

and all those ones that are being added, and why they're all sprinkled around:

=1+SUMPRODUCT(($S$1:$S$4*10^5+$L$1:$L$4+1+$O$1:$O$4+1+$Q$1:$Q$4+1-ROW($S$1:$S$4)/1000>$S1*10^5+$L1+1+$O1+1+$Q1+1-ROW($S1)/1000)+0)

I can only speak for myself but the only way I'm going to understand this enough to help fix it is if you start from the beginning and describe what you're trying to do--what the columns mean and how that relates to your rationale for ranking. And then explain how you want this formula to do it for you.

[daddylonglegs]

I think this is an example of a technique used for ranking where you add the tiebreak columns to the original score (and original score is multiplied by a large number to ensure that the tie-breaks don't have enough effect to rank a lower score higher). The ROW function is then used to add small values (hence division by 1000) to the score, presumably to ensure that you don't get repeat ranks.

In this case I imagine that 1000 isn't large enough, so the difference produced by the tie-break columns is unintentionally offset by the difference from the ROW function. Try using a larger value like 10^9 in place of 1000 i.e.

=1+SUMPRODUCT(($S$1:$S$4*10^5+$L$1:$L$4+1+$O$1:$O$4+1+$Q$1:$Q$4+1-ROW($S$1:$S$4)/10^9>$S1*10^5+$L1+1+$O1+1+$Q1+1-ROW($S1)/10^9)+0)

However it's still a little unclear what the intent is (as 6StringJazzer says) so I can't be sure that would work in all scenarios. Also there are probably better ways (in terms of efficiency and robustness) to do this, so if you care to explain what you need to do in words then somebody might be able to suggest a better way.

At the moment your formula isn't prioritising any of L, O and Q, just using the combined amounts. Should the tie-break take these in order?