Flaw in my logic...

[How_Do_I]

This is a strange one… somewhere along the lines there is a flaw in my logic… I’ve spent a long time thinking about this and I just can not see where I’m going wrong…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I’m working on the basis of in a football league of 24 teams every team will play each other in week 1 to week 23… At week 24 the reverse fixtures commence… (Team A v Team B… the reverse fixture is Team B v Team A)…
<o></o>
If I drag the formulas across in my 1<SUP>st</SUP> Jeanie to Week 46 and then SUM the highlighted ROWS I get a set of values…
<o></o>

Excel Workbook
	BR	BS
6	Away Win	2
7	Away Draw	0
8	Away Loss	2
9	Reverse Home Draw	6
10	Away Win	2
11	Away Draw	3
12	Away Loss	1
13	Reverse Home Loss	2
14	Away Win	0
15	Away Draw	0
16	Away Loss	2
17	Week	24
Six Points

<o></o>
In the Jeanie below I have used a different formulas as this time I am trying to get the data by teams and not weeks. If I drag the formulas across for the 24 teams and SUM the rows I get the SAME results as in the 1<SUP>st</SUP> Jeanie….
<o></o>
However if I SUM the columns in this Jeanie why don’t I get 12 for the 12 reverse fixtures played by each time… Note if I total the SUMS (at the end of each ROW I get 276 which is correct i.e. 276 reverse fixtures… My problem is, why don’t the columns of the 2<SUP>nd</SUP> Jeanie add up to 12… What am I missing please?
<o></o>

Excel Workbook
	AJ	AK	AL
36	Reverse Home Win
37	Home Win	2	3
38	Home Draw	1	0
39	Home Loss	3	3
40	Reverse Home Draw
41	Home Win	2	2
42	Home Draw	0	1
43	Home Loss	0	0
44	Reverse Home Loss
45	Home Win	1	1
46	Home Draw	2	0
47	Home Loss	0	2
Output

 

[Firefly2012]

If you have 24 teams and they play each other each week, won't you have completed the first set of fixtures in week 12? Then the reverse fixtures will have completed by week 24?

EDIT: Ach no, my logic is more deeply flawed than yours. 23 weeks it is.

 

[How_Do_I]

I’m missing something blatantly obvious here…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
From week 24 to week 46 there are 23 weeks and 12 games per week so 23*12=276 games…
<o> </o>
So if the sum of each column was to be 12… 24 teams x 12 results = 288 games which is wrong as my total should be 276…
<o> </o>
I just can’t see how some team totals (the column totals) are 11 and some are 12… What am I missing please?
<o> </o>

Excel Workbook
	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM
3	Barnsley	Bristol City	Burnley	Cardiff City	Coventry City	Crystal Palace	Derby County	Doncaster Rovers	Hull City	Ipswich Town	Leeds Utd	Leicester City	Middlesbrough	Millwall	Norwich City	Nottingham Forest	Portsmouth	Preston North End	QPR	Reading	Scunthorpe United	Sheffield United	Swansea City	Watford
4	11	12	12	11	12	11	11	12	12	11	12	11	11	12	12	12	11	12	11	11	11	11	12	12	276
Output

 

[How_Do_I]

If you have 24 teams and they play each other each week, won't you have completed the first set of fixtures in week 12? Then the reverse fixtures will have completed by week 24?

Hello there...

No, 24 teams means that they play 23 teams at home and 23 games away (23 as they don’t play themselves) so there are 46 games in a season which is correct for a 24 team league.

 

[taurean]

Very interesting.
Just one thing: 12 + 11 = 23 number of games that a team plays.
And you have 12 teams with total as 11 and rest 12 have total as 12.

How do you arrive to the value at BO4?

 

[How_Do_I]

Very interesting.
Just one thing: 12 + 11 = 23 number of games that a team plays.
And you have 12 teams with total as 11 and rest 12 have total as 12.

How do you arrive to the value at BO4?

Hello there.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I used the Jeanie you refer to as an example… this is how I’d get the value 11 in BO4 and 12 in BP 14
<o> </o>

Excel Workbook
	AJ	AK	AL
36	Reverse Home Win
37	Home Win	2	3
38	Home Draw	1	0
39	Home Loss	3	3
40	Reverse Home Draw
41	Home Win	2	2
42	Home Draw	0	1
43	Home Loss	0	0
44	Reverse Home Loss
45	Home Win	1	1
46	Home Draw	2	0
47	Home Loss	0	2
48
49	Reverse Played	11	12
Output

<o> </o>
<o> </o>

 

[How_Do_I]

Is there a difference between this when written in a formula…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
If I want to use Week 24 I’d use Week = 24 as part of my SUMPRODUCT i.e. ,--(Week = 24)
<o> </o>
What about if I want to write week = 24 or higher… I’m writing that as ,--(Week>=24) is that right please?

Should there be a “ and “ in there please?

 

[How_Do_I]

I am missing something as I just can not see why some totals are 11 and some totals are 12 BUT, the following would suggest that it is correct...

Excel Workbook
	A	B	C	D	E	F	G
1	Week	Home	Away		Week	Home	Away
2	2	Barnsley	Crystal Palace		1	Bristol City	Millwall
3	4	Barnsley	Middlesbrough		3	Bristol City	Barnsley
4	6	Barnsley	Leeds Utd		6	Bristol City	Watford
5	7	Barnsley	Derby County		7	Bristol City	Coventry City
6	10	Barnsley	Cardiff City		10	Bristol City	Norwich City
7	11	Barnsley	Nottingham Forest		12	Bristol City	Reading
8	14	Barnsley	Hull City		13	Bristol City	QPR
9	15	Barnsley	Leicester City		15	Bristol City	Preston North End
10	18	Barnsley	Portsmouth		18	Bristol City	Leicester City
11	19	Barnsley	Watford		19	Bristol City	Sheffield United
12	21	Barnsley	Sheffield United		21	Bristol City	Derby County
13	23	Barnsley	Burnley		24	Bristol City	Crystal Palace
14	26	Barnsley	Coventry City		25	Bristol City	Cardiff City
15	28	Barnsley	Swansea City		27	Bristol City	Middlesbrough
16	29	Barnsley	Preston North End		29	Bristol City	Swansea City
17	31	Barnsley	Ipswich Town		31	Bristol City	Leeds Utd
18	34	Barnsley	Norwich City		34	Bristol City	Scunthorpe United
19	36	Barnsley	Scunthorpe United		36	Bristol City	Portsmouth
20	38	Barnsley	Reading		38	Bristol City	Burnley
21	40	Barnsley	Bristol City		39	Bristol City	Doncaster Rovers
22	41	Barnsley	QPR		42	Bristol City	Ipswich Town
23	44	Barnsley	Doncaster Rovers		44	Bristol City	Nottingham Forest
24	46	Barnsley	Millwall		46	Bristol City	Hull City
25
26	1	QPR	Barnsley		2	Doncaster Rovers	Bristol City
27	3	Bristol City	Barnsley		4	Ipswich Town	Bristol City
28	5	Norwich City	Barnsley		5	Scunthorpe United	Bristol City
29	8	Reading	Barnsley		8	Burnley	Bristol City
30	9	Scunthorpe United	Barnsley		9	Portsmouth	Bristol City
31	12	Burnley	Barnsley		11	Cardiff City	Bristol City
32	13	Coventry City	Barnsley		14	Middlesbrough	Bristol City
33	16	Preston North End	Barnsley		16	Swansea City	Bristol City
34	17	Ipswich Town	Barnsley		17	Leeds Utd	Bristol City
35	24	Swansea City	Barnsley		22	Hull City	Bristol City
36	25	Nottingham Forest	Barnsley		23	Reading	Bristol City
37	27	Hull City	Barnsley		26	QPR	Bristol City
38	20	Doncaster Rovers	Barnsley		28	Crystal Palace	Bristol City
39	22	Millwall	Barnsley		20	Nottingham Forest	Bristol City
40	30	Leicester City	Barnsley		30	Preston North End	Bristol City
41	32	Portsmouth	Barnsley		32	Leicester City	Bristol City
42	33	Leeds Utd	Barnsley		33	Watford	Bristol City
43	35	Derby County	Barnsley		35	Coventry City	Bristol City
44	37	Cardiff City	Barnsley		37	Norwich City	Bristol City
45	39	Crystal Palace	Barnsley		40	Barnsley	Bristol City
46	42	Middlesbrough	Barnsley		41	Millwall	Bristol City
47	43	Watford	Barnsley		43	Sheffield United	Bristol City
48	45	Sheffield United	Barnsley		45	Derby County	Bristol City
Sheet1

 

[taurean]

Does this distribution of gets exactly "reversed"? i.e. the teams that show total as 11 in the "First Half" show total as 12 in the "Second Half" and vice versa.

If that is the case then the formula is "trying" to even the "odd" number 23 as you can't have 11.5 as total (which will be absurd in this context) in season half.

Just for the sake if there were 25 teams then would it give 12 as total?

 

[How_Do_I]

Yes the distribution is even exactly along the lines as you wrote…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
In the Jeanie in post #8 you can see that in C26:C48 there are 12 Barnsley that have 24 or above in A26:A48 where for <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on"><st1:PlaceName w:st="on">Bristol</st1:PlaceName> <st1:PlaceType w:st="on">City</st1:PlaceType></st1lace> there are 11 that meet the criteria…
<o></o>
I just thought they’d be the same amount of games per team per half season for some reason but, there isn’t and I can’t think of an explanation for that.
As you said if there are 23 weeks until half season then a team can not play 11.5 match though...