Sports tournament league calculator - differentiating Concerned Teams

[osianap]

No VBA solutions please...


It's quite easy to buid an Excel calculator for a league table, where teams on equal points are differentiated according to the number of goals socred and conceded.

However, some tournaments follow a set of rules whereby only games between the "Concerned Teams" are considered in calculating the difference.

Consider the following example. Note that team Gamma have the best goal difference when considering all games. However, since the rules dictate that only matches between "Concerned Teams" should be considered in making the differentiation, Gamma's 17-0 score is not taken into consideration, and they should therefore come 3rd in the league.

Excel 2012

	B	C	D	E	F
Beta
Delta
Delta
Gamma
Alpha
Delta

<tbody>
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: right"]Alpha[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: right"]Gamma[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: right"]Alpha[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: right"]Beta[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: right"]Gamma[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: right"]Beta[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]

</tbody>

Sheet1​

My current calculations work by taking note of all games in the tournament, and therefore ends up with the wrong result (i.e. Gama is ranked 1st in column U, when they should be 3rd).

Excel 2012

	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
Alpha
Beta
Gamma
Delta

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]-21[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1212[/TD]
[TD="align: center"]10100.01212[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]1010[/TD]
[TD="align: center"]620204.01010[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]909[/TD]
[TD="align: center"]640418.00909[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]808[/TD]
[TD="align: center"]630304.00808[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]W2[/TH]
[TD="align: left"]=COUNTIF(ResultCells, V2&"_win")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]X2[/TH]
[TD="align: left"]=COUNTIF(ResultCells, V2&"_draw")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Y2[/TH]
[TD="align: left"]=COUNTIF(ResultCells, V2&"_lose")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Z2[/TH]
[TD="align: left"]=SUMIF(GameHomeTeams,V2,GameRes1) + SUMIF(GameAwayTeams,V2,GameRes2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AA2[/TH]
[TD="align: left"]=SUMIF(GameHomeTeams,V2,GameRes2)+SUMIF(GameAwayTeams,V2,GameRes1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AB2[/TH]
[TD="align: left"]=Z2-AA2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AC2[/TH]
[TD="align: left"]=RANK.EQ(AB2,$AB$2:$AB$5,1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AD2[/TH]
[TD="align: left"]=W2*3 + X2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]W3[/TH]
[TD="align: left"]=COUNTIF(ResultCells, V3&"_win")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]X3[/TH]
[TD="align: left"]=COUNTIF(ResultCells, V3&"_draw")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Y3[/TH]
[TD="align: left"]=COUNTIF(ResultCells, V3&"_lose")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Z3[/TH]
[TD="align: left"]=SUMIF(GameHomeTeams,V3,GameRes1) + SUMIF(GameAwayTeams,V3,GameRes2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AA3[/TH]
[TD="align: left"]=SUMIF(GameHomeTeams,V3,GameRes2)+SUMIF(GameAwayTeams,V3,GameRes1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AB3[/TH]
[TD="align: left"]=Z3-AA3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AC3[/TH]
[TD="align: left"]=RANK.EQ(AB3,$AB$2:$AB$5,1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AD3[/TH]
[TD="align: left"]=W3*3 + X3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]W4[/TH]
[TD="align: left"]=COUNTIF(ResultCells, V4&"_win")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]X4[/TH]
[TD="align: left"]=COUNTIF(ResultCells, V4&"_draw")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Y4[/TH]
[TD="align: left"]=COUNTIF(ResultCells, V4&"_lose")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Z4[/TH]
[TD="align: left"]=SUMIF(GameHomeTeams,V4,GameRes1) + SUMIF(GameAwayTeams,V4,GameRes2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AA4[/TH]
[TD="align: left"]=SUMIF(GameHomeTeams,V4,GameRes2)+SUMIF(GameAwayTeams,V4,GameRes1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AB4[/TH]
[TD="align: left"]=Z4-AA4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AC4[/TH]
[TD="align: left"]=RANK.EQ(AB4,$AB$2:$AB$5,1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AD4[/TH]
[TD="align: left"]=W4*3 + X4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]W5[/TH]
[TD="align: left"]=COUNTIF(ResultCells, V5&"_win")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]X5[/TH]
[TD="align: left"]=COUNTIF(ResultCells, V5&"_draw")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Y5[/TH]
[TD="align: left"]=COUNTIF(ResultCells, V5&"_lose")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Z5[/TH]
[TD="align: left"]=SUMIF(GameHomeTeams,V5,GameRes1) + SUMIF(GameAwayTeams,V5,GameRes2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AA5[/TH]
[TD="align: left"]=SUMIF(GameHomeTeams,V5,GameRes2)+SUMIF(GameAwayTeams,V5,GameRes1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AB5[/TH]
[TD="align: left"]=Z5-AA5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AC5[/TH]
[TD="align: left"]=RANK.EQ(AB5,$AB$2:$AB$5,1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AD5[/TH]
[TD="align: left"]=W5*3 + X5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]U2[/TH]
[TD="align: left"]=RANK(AF2,$AF$2:$AF$5,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]U3[/TH]
[TD="align: left"]=RANK(AF3,$AF$2:$AF$5,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]U4[/TH]
[TD="align: left"]=RANK(AF4,$AF$2:$AF$5,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]U5[/TH]
[TD="align: left"]=RANK(AF5,$AF$2:$AF$5,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AF2[/TH]
[TD="align: left"]=(AD2*100000) + (AC2*10000) + (AC2*100) + Z2 + (AE2/100000)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AF3[/TH]
[TD="align: left"]=(AD3*100000) + (AC3*10000) + (AC3*100) + Z3 + (AE3/100000)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AF4[/TH]
[TD="align: left"]=(AD4*100000) + (AC4*10000) + (AC4*100) + Z4 + (AE4/100000)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AF5[/TH]
[TD="align: left"]=(AD5*100000) + (AC5*10000) + (AC5*100) + Z5 + (AE5/100000)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]GameAwayTeams[/TH]
[TD="align: left"]=Sheet1!$F$2:$F$13[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]GameHomeTeams[/TH]
[TD="align: left"]=Sheet1!$C$2:$C$13[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]GameRes1[/TH]
[TD="align: left"]=Sheet1!$R$2:$R$13[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]GameRes2[/TH]
[TD="align: left"]=Sheet1!$S$2:$S$13[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]ResultCells[/TH]
[TD="align: left"]=Sheet1!$P$2:$Q$13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]





The question threfore is... how does one build a ranking system (in column U) which considers "Concerned Teams" when points are equal?

To avoid ambiguity... one could end up with the following types of ties that would all be calculated the same way:

1. All 4 teams on equal points
2. 3 teams on equal points (either joint 1st or joint 2nd)
3. 2 teams on equal points (either joint 1st, joint 2nd, or joint 3rd)
4. 2 lots of 2 teams on equal points (joint 1st and joint 3rd)

I know the answer isn't simple!! Describing the principles of the algorithm in words, is as good (if not better) than showing the algorithm in Excel cells and functions.

Thanks!!

 

[oldbrewer]

[TABLE="width: 1190"]
<colgroup><col span="3"><col><col span="3"><col><col span="10"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]POINTS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]3[/TD]
[TD]D[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]1[/TD]
[TD]D[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]C[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]3[/TD]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]D[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]0[/TD]
[TD]D[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]D[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]3[/TD]
[TD]D[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]0[/TD]
[TD]C[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]GOALS FOR[/TD]
[TD][/TD]
[TD="colspan: 2"]GOALS AGAINST[/TD]
[TD][/TD]
[TD]POINTS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]TO SEPARATE C AND D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]only goal difference for C and D match is used[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]teams to separate[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="colspan: 5"]this area will only record goals for C and D games[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]goals for[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]goals against[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="colspan: 7"]in this simple example they are obviously matched on goal difference[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="colspan: 3"]but the principal holds[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="colspan: 4"]if the teams played each other twice[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]there would be another set of goals for and against for C and D games[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[osianap]

@oldbrewer

Well, yes. I understand that's how the calculation is done in principle. I explained that in the questoin.
In attempting to answer the question though, the point you need to elaborate on, is how to extrapolate the C-D match into its own league table, and to do so dynamically in Excel (keeping in mind that there may be multiple ties in a single group).

Perhaps there is some missing detail in the way you copy-pasted the cells into the forum.
However, in its current form, your answer doesn't help me to get to a solution in Excel.

 

[wideboydixon]

Here's what I did:

Added a new column at AF
Changed AC2 formula to: =RANK.EQ(AD2,$AD$2:$AD$5,0) - copied down to row 5
Added a new named range called HomeRanking in cells AI2:AI13. Formula for AI2: =IFNA(VLOOKUP(C2, $V$2:$AG$5, 8,FALSE),0) - copied down to row 13
Added a new named range called AwayRanking in cells AJ2:AJ13. Formula for AJ2: =IFNA(VLOOKUP(F2, $V$2:$AG$5, 8,FALSE),0) - copied down to row 13
Changed AE2 formula to: =SUMPRODUCT((GameHomeTeams=V2)*(GameRes1)*(HomeRankings=AC2)*(AwayRankings=AC2))+SUMPRODUCT((GameAwayTeams=V2)*(GameRes2)*(HomeRankings=AC2)*(AwayRankings=AC2)) - copied down to row 5
Changed AF2 formula to: =SUMPRODUCT((GameHomeTeams=V2)*(GameRes2)*(HomeRankings=AC2)*(AwayRankings=AC2))+SUMPRODUCT((GameAwayTeams=V2)*(GameRes1)*(HomeRankings=AC2)*(AwayRankings=AC2)) - copied down to row 5
Changed AG2 formula to: =(AD2 * 100000) + (W2 * 10000) + ((AE2 - AF2) * 100) + AB2 + (ROW() / 100) - copied down to row 5

That gave me the proper ranking in column U

WBD

 

[osianap]

@wideboydixon

Thanks. The principle of using the SUMPRODUCT() function to match up all teams with the same points, works perfectlly.

There are a few things in the details of your answer that are a little wrong, (in fact, you have to use the same principle on other columns, not just the goals-scored columns as you've done). Nevertheless, you have answered the questoin of how to draw the same calculations on only the concerned games.

Thanks,