SUMPRODUCT function

[artoo]

The formula

=SUMPRODUCT((Sched[Home]=[@Club])*(Sched[hScore]>Sched[vScore])+(Sched[VIS]=[@Club])*(Sched[vScore]>Sched[hScore]))

is placed in the "W" column and it totals the numer of wins for that team in the table shown below

Rank   Club     GP  W  L   Percentage  Rank Pts
   4   Team A  162 73 89      0.451         5
   5   Team C  162 84 78      0.519         2
   1   Team D  162 85 77      0.525         1
   3   Team B  162 84 78      0.519         2
   2   Team E  162 74 88      0.457         4

In the above example Team B and Team C are tied with the same record, and to break the tie, I'll be adding a column "DivW" that will determine the total the number of wins against teams within the division using the formula

=SUMPRODUCT((Sched[Home]=[@Club])*(Sched[hScore]>Sched[vScore])*( { is Sched[VIS] in same division })+(Sched[VIS]=[@Club])*(Sched[vScore]>Sched[hScore])*( { is Sched[Home] in same division }))


Any suggestions on replacing the text between {} to determine if the opponent is in the same division as [@Club] ?

 

[DRSteele]

Not having the data to tinker with, this will be tough but I'll try anyway.

I would suggest that you add three new columns to your ExcelTable called SCHED: HDiv and VDiv and DivGm
Of course, you will need some sort of VLOOKUP formula in HDiv and VDiv columns to look up each row's Home team and Visitor team in a reference table that lists each team's division. In the new DivGm column, put the formula =IF([@Vdiv]=[@Hdiv],"Div","")


Then in the first SUMPRODUCT (i.e., the team's home wins) substitute this for your red stuff, retaining the parentheses but omitting those squiggly braces: Sched[DivGm]="Div"
and in the second SUMPRODUCT (i.e., the team's visiting wins) do the same: Sched[DivGm]="Div"
Those arrays should determine whether the club's game is a divisional one.

 

[artoo]

Thanks for the awesome reply DRSteele!!!!!!!

 

[DRSteele]

You're welcome. I hope it works for you.

Don't be shy about coming back for more help on things like breaking ties using successive rules, like those in the NHL. I've done several posts on the proper methodology that just might be helpful for you.