Tie-Breaker in Ranking

[dotcanada]

Good day.

I've been pulling my hair out with this problem for some time.

I have a file (sorry - it's pretty large at about 10mb so I can't attach it) which ranks the NHL teams by division, conference, and league.

I also have a cross-reference chart with all the teams down along the left and along the top (partial example below: 1=Leading Series, 0=Trailing Series, T=Series tied) which auto-updates when I place in the scores.

[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Ducks
[/TD]
[TD]Coyotes
[/TD]
[TD]Bruins
[/TD]
[/TR]
[TR]
[TD]Ducks
[/TD]
[TD]-
[/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Coyotes
[/TD]
[TD]1
[/TD]
[TD]-
[/TD]
[TD]T
[/TD]
[/TR]
[TR]
[TD]Bruins
[/TD]
[TD][/TD]
[TD]T
[/TD]
[TD]-
[/TD]
[/TR]
</tbody>[/TABLE]


Each of the standings has a ranking formula (below is a partial example one of the standings):


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Rank
[/TD]
[TD]Team
[/TD]
[TD]GP
[/TD]
[TD]W
[/TD]
[TD]L
[/TD]
[TD]OT
[/TD]
[TD]ROW
[/TD]
[TD]GF
[/TD]
[TD]GA
[/TD]
[TD]Diff
[/TD]
[TD]Pts
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]Ducks
[/TD]
[TD]61
[/TD]
[TD]34
[/TD]
[TD]19
[/TD]
[TD]8
[/TD]
[TD]32
[/TD]
[TD]153
[/TD]
[TD]144
[/TD]
[TD]+9
[/TD]
[TD]76
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]Coyotes
[/TD]
[TD]63
[/TD]
[TD]27
[/TD]
[TD]30
[/TD]
[TD]6
[/TD]
[TD]26
[/TD]
[TD]168
[/TD]
[TD]198
[/TD]
[TD]-30
[/TD]
[TD]60
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]Bruins
[/TD]
[TD]64
[/TD]
[TD]35
[/TD]
[TD]23
[/TD]
[TD]6
[/TD]
[TD]31
[/TD]
[TD]197
[/TD]
[TD]176
[/TD]
[TD]+21
[/TD]
[TD]76
[/TD]
[/TR]
</tbody>[/TABLE]









In cell A2, I would have =RANK.EQ(K2,$K$2:$K$4,0)+SUMPRODUCT(--(K2=K$2:K$4),--(C2>C$2:C$4))+SUMPRODUCT(--(K2=K$2:K$4),--(C2=C$2:C$4),--(G2<G$2:g$4))+sumproduct(--(k2=k$2:k$4),--(c2=c$2:c$4),--(g2=g$2:g$4),--(j2<j$2:j$4)) down.

I need to further rank the teams that if two teams are tied in column G, then whoever is leading in their respective series (from the first table) would be ranked above the other.

Any help would be greatly appreciated.

Thanks in advance.</G$2:g$4))+sumproduct(--(k2=k$2:k$4),--(c2=c$2:c$4),--(g2=g$2:g$4),--(j2<j$2:j$4))>

 

[dotcanada]

Sorry. For some reason, the rank formula got cut off. Here it is again in cell A2:

=RANK.EQ(K2,$K$2:$K$4,0)+SUMPRODUCT(--(K2=K$2:K$4),--(C2>C$2:C$4))+SUMPRODUCT(--(K2=K$2:K$4),--(C2=C$2:C$4),--(G2<G$2:G$4))+SUMPRODUCT(--(K2=K$2:K$4),--(C2=C$2:C$4),--(G2=G$2:G$4),--(J2<J$2:J$4))

 

[dotcanada]

Apologies. It keeps getting cut off. That last little part is supposed to be (G2"<"G$2:G$4),SUMPRODUCT(--(K2=K$2:K$4),--(C2=C$2:C$4),--(G2=G$2:G$4),--(J2"<"J$2:J$4)).

Seems like posting a "<" (without the quotes cuts stuff off afterwards)

 

[FDibbins]

Try 1 of these...
Rank without missing numbers =RANK($H5,$H$5:$H$25,1)+COUNTIF($H$5:H5,H5)-1
rank with missing numbers =SUMPRODUCT((A2>A$2:A$7)/COUNTIF(A$2:A$7,A$2:A$7))+1

 

[Peter_SSs]

Seems like posting a "<" (without the quotes cuts stuff off afterwards)

Only if the < is followed immediately by a letter. In that case, just add a space in between:

=RANK.EQ(K2,$K$2:$K$4,0)+SUMPRODUCT(--(K2=K$2:K$4),--(C2>C$2:C$4))+SUMPRODUCT(--(K2=K$2:K$4),--(C2=C$2:C$4),--(G2< G$2:g$4))+sumproduct(--(k2=k$2:k$4),--(c2=c$2:c$4),--(g2=g$2:g$4),--(j2< j$2:j$4))

 

[dotcanada]

Try 1 of these...
Rank without missing numbers =RANK($H5,$H$5:$H$25,1)+COUNTIF($H$5:H5,H5)-1
rank with missing numbers =SUMPRODUCT((A2>A$2:A$7)/COUNTIF(A$2:A$7,A$2:A$7))+1

Thanks for your help FDibbins. However, I'm a little confused by your example. Where are the cell numbers referring to in this case (ie, H5). Is it to the first table?

Let's say the first table had these cells (sorry for not including these earlier):

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Ducks[/TD]
[TD]Coyotes[/TD]
[TD]Bruins[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ducks[/TD]
[TD]-[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Coyotes[/TD]
[TD]1[/TD]
[TD]-[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Bruins[/TD]
[TD]0[/TD]
[TD]T[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]










And here is the full rank formula in cell A2 in the 2nd table (thanks Peter for the info):

=RANK.EQ(K2,$K$2:$K$4,0)+SUMPRODUCT(--(K2=K$2:K$4),--(C2>C$2:C$4))+SUMPRODUCT(--(K2=K$2:K$4),--(C2=C$2:C$4),--(G2< G$2:g$4))+sumproduct(--(k2=k$2:k$4),--(c2=c$2:c$4),--(g2=g$2:g$4),--(j2< j$2:j$4)).

I want to be able to place something between "SUMPRODUCT(--(K2=K$2:K$4),--(C2=C$2:C$4),--(G2< G$2:g$4))" and "+sumproduct(--(k2=k$2:k$4),--(c2=c$2:c$4),--(g2=g$2:g$4),--(j2< j$2:j$4))". So basically between the last two SUMPRODUCT formulas as that's the order of the tie-breaker I need to go by.

Thanks again for all your help.

 

[dotcanada]

I think I may have added to this a little bit. However, I don't know how to incorporate it into my rank formula.

I'm using a cross-reference formula to pin-point the character in the first table.

So, for example, using the first a table, I can use =INDEX(N5:P7,MATCH("Ducks",M5:M7,0),MATCH("Bruins",N4:P4,0))

This should give me a result of 1.

Now, how would I incorporate that into my rank function?

One thing I didn't state is there are 4 rules for tie-breakers in the rankings (which indicates in the rank formula):
1. Team with the fewest games played
2. Most regulation/overtime wins (ROW)
3. If two teams are tied, whoever has the most points in head-to-head play (ie, whoever is leading the series between the two teams in the regular season).
4. Highest goals-for/goals-against differential (Diff).

Any help would be greatly appreciated.