Tie Breaker

[Johnmus]

Hi everyone,

I am using the following formula to rank within different categories in Column A (MSMI is one of four). This works great except when there is a tie it shows duplicate ranks. Is there anyway to add a tie-breaker to this formula?

=IF(A4="MSMI",SUMPRODUCT((A$4:A$75="MSMI")*(I$4:I$75>I4)/COUNTIFS(A$4:A$75,A$4:A$75&"",I$4:I$75,I$4:I$75&""))+1,"")

Any help is appreciated.

John

 

[shawnhet]

I'm not sure exactly what the output is going to look like here in terms of whether the rankings are integers, but if you were to replace the +1 in your expression with RAND()(or 1+RAND()or whatever).

This will add a random number on top of the calculation you're performing on each MSMI member. Thus, even if the value of the calculation is identical, the ranking will be different(because a random number is added on top of this). however, this may not be what you want - rankings won't be integers any more.

Cheers,

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean? This is just an artificial tie-breaker. Or did you intend using data in another column to break any ties?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
4	MSMI								6	2
5	abc								6
6	MSMI								8	1
7	MSMI								4	5
8	MSMI								6	3
9	abc								3
10	abc								2
11	MSMI								6	4
12	MSMI								3	6
13
Rank

 

[Johnmus]

Thank you both for the help. Yes, indeed I wish to break the tie with data in column D. Sorry I did not mention that. The artificial tie breaker will work but will not justify the reason for breaking the tie.

 

[Peter_SSs]

Yes, indeed I wish to break the tie with data in column D.

Then perhaps you could give some indication of the sort of data in column D and how it would break a tie? Is is alphabetic with key letters/words deciding the rank or is it numeric with higher values (or lower?) resulting in a higher overall rank?

Unless you tell us clearly, all we can do is guess (and that didn't work for me the first time )

 

[Johnmus]

Sorry for not being more explicit. The data in column D is numeric - scores for competitions - the higher score in column D will break the tie.

Thanks again!

John

 

[shawnhet]

Rather than using your framework, I came up with my own here that I think should do what you want:

=IF(A4="MSMI",RANK(I4,$I$4:$I$75,1)+(RANK(D4,$D$4:$D$75,1)/100),0)

This line assigns a rank in ascending order for each MSMI member composed of the actual rank in terms of I values plus 1/100 of the rank in terms of the D values(this amount is simply to break any ties).

=IF(A4="MSMI",RANK(Q4,$Q4:$Q$75,0),"") ( above formula placed in cell Q in this example).

This ranks in descending order the numbers created in the previous cell. This should give you a listing with the highest value in I listed as 1 with any ties broken. Obviously, it will not work if you two records have identical I and D values(that will still give you a tie).

Cheers,

 

[Johnmus]

That works! Creating a helper column makes sense.

Thanks for the help!

John

 

[Peter_SSs]

I think you could use a simpler formula in your helper column. (What you need to divide by in the helper column depends on the type of numbers you have in columns I and D.)

Excel Workbook
	A	D	I	J	Q
4	MSMI	3	6	3	6.003
5	abc	7	6
6	MSMI	2	8	1	8.002
7	MSMI	3	4	5	4.003
8	MSMI	4	6	2	6.004
9	abc	8	3
10	abc	1	2
11	MSMI	3	6	3	6.003
12	MSMI	8	3	6	3.008
13
Rank

 

[Johnmus]

Yes, this seems a bit simpler. Thank you both for your help.

It is much appreciated!

John