Football Form Table

[kingspur06]

Hi everyone,
I have created a spreadsheet which calculates league standings based on a table of match results which works really well. It shows the usual info, played, won, drawn, lost, goals scored etc etc, and I have also broken this down into home and away matches.
This is fine at showing the current league position for all matches played in the season so far.

My problem is that I am also looking to create a recent form table which only takes into account the last 6 matches for each side. I just cannot work out how to adjust my current league table to only account for the last 6 results for each team.

My 'FIXTURES' sheet is laid out like this:

A: DATE B: HOME TEAM C: HOME SCORE D: AWAY SCORE E: AWAY TEAM

And as an example, the formula I have used to display the number of home wins for Team A in my league table is:

=SUMPRODUCT((FIXTURES!$B$2:$B$381='DUMMY TABLE'!$B4)*(FIXTURES!$C$2:$C$381>FIXTURES!$D$2:$D$381))

where DUMMY TABLE B4 = the team name.

I would appreciate any guidance possible on how I can limit the formula result to only the last six matches played

many thanks in advance
Mark

 

[How_Do_I]

I give every game of the season a number... A match from week one of the season will be game 1... A match from the last week of the championship will be game 552...

So if you take say Bristol City and find their largest number that is their last game played... The second to largest number is their second to last game etc...

Then these are the formulas you'll need...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Ref	Week	Home	Away	HTHome	HTAway	Date	FTHome	FTAway	HT	FT	HT FT	Away HT	Away FT	Away HT FT
2	5	1	Bristol City	Ipswich Town	0	1	06-Aug-11	0	3	L	L	LL	W	W	WW		Last 3 Games	3
3	37	4	Bristol City	Portsmouth	0	0	20-Aug-11	0	0	D	D	DD	D	D	DD					P	W	D	L	F	A	Min	Max
4	62	6	Bristol City	Brighton & Hove	0	0	10-Sep-11	0	1	D	L	DL	D	W	DW		Bristol City	103		3	0	1	2	3	5	62	103
5	87	8	Bristol City	Hull City	0	0	24-Sep-11	1	1	D	D	DD	D	D	DD			87
6	103	9	Bristol City	Reading	1	0	27-Sep-11	2	3	W	L	WL	L	W	LW			62
Sheet1

 

[kingspur06]

Hi - many thanks for your response.

I copied your example and copied the formula across but I only get a #NAME? error come up.
Am I doing something wrong?
thanks
Mark

 

[wigi]

Hello

2 things that you should be aware of when copying the example above:

- did you use the same named ranges? Like Home, Ref, and so on.
- array formulas are entered as Ctrl-Shift-Enter, not just Enter. But I bet that the previous part already solves your issue.

 

[kingspur06]

Hi - yes I copied the example in the first response so all of the headings and cell references were exactly the same.

So are you saying that I should not have copied over the headings?

 

[wigi]

If you copied the formulas without editing, do you have named ranges called Home, Ref, FTHome, FTAway?

 

[kingspur06]

Yes I copied the example data with headings exactly as they are shown

 

[wigi]

Again, the formulas will not work if you do not create the named ranges. I listed them in my earlier post. Do you understand the formulas that were given to you? Do you know what an array formula is?

 

[How_Do_I]

Hi Mark,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
As Wigi has mentioned you need to create named ranges. Note how no other data touches the results in my example. There is an empty column after the data (Column P)… So ensuring that no other data touches that “block” of results do this…
<o> </o>
Click in any cell within that block.
Press the “Ctrl” key and the “*” key.
<o> </o>
That will highlight the block of results.
<o> </o>
Now press the “Ctrl” Shift” and “F3” Keys.
The create names box will appear now… you need to make sure that only the create names in “Top row” is selected… Press OK… You’ve now created the named ranges…
<o> </o>
If you were setting this up from start you’d have the headings and for a 24 team league you’d have 1-552 in the ref column… Doing the above now would set everything up.
<o> </o>
If you are still getting the NAME error, you’ll have to read up about array formulas… The formulas in my example that have these brackets around them “{ }” are array formulas… You need to copy the formula but not the brackets and instead of entering the formula with your “Enter” key, you use “Ctrl” “Shift” and “Enter”…
<o> </o>
Hope this helps you.