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
2	A	Alpha	0	2	Beta
3		Gamma	1	0	Delta
4		Alpha	0	2	Delta
5		Beta	2	0	Gamma
6		Gamma	17	0	Alpha
7		Beta	0	2	Delta

<tbody>
</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
2	4	Alpha	0	0	3	0	21	-21	1	0	1212	10100.01212
3	3	Beta	2	0	1	4	2	2	2	6	1010	620204.01010
4	1	Gamma	2	0	1	18	2	16	4	6	909	640418.00909
5	2	Delta	2	0	1	4	1	3	3	6	808	630304.00808

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

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

<tbody>
</tbody>

Workbook Defined Names Name Refers To GameAwayTeams =Sheet1!$F$2:$F$13 GameHomeTeams =Sheet1!$C$2:$C$13 GameRes1 =Sheet1!$R$2:$R$13 GameRes2 =Sheet1!$S$2:$S$13 ResultCells =Sheet1!$P$2:$Q$13 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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]

						POINTS
A	B	3	1		A	3	D	0
C	D	2	2		C	1	D	1
A	C	1	0		A	3	C	0
B	D	0	1		B	0	D	3
A	D	2	1		A	3	D	0
B	C	0	3		B	0	C	3
		GOALS FOR		GOALS AGAINST		POINTS
		A	6		2			9
		B	1		7			0
		C	5		3			4
		D	4		4			4
				TO SEPARATE C AND D
				only goal difference for C and D match is used
				teams to separate	C	D
				C	D			C	D
											this area will only record goals for C and D games
			goals for	2	2		goals against	2	2
											in this simple example they are obviously matched on goal difference
											but the principal holds
											if the teams played each other twice
											there would be another set of goals for and against for C and D games

<colgroup><col span="3"><col><col span="3"><col><col span="10"></colgroup><tbody>
</tbody>

 

[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,