Football Form Table

kingspur06

New Member
Joined
Apr 24, 2007
Messages
28
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,812
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1RefWeekHomeAwayHTHomeHTAwayDateFTHomeFTAwayHTFTHT FTAway HTAway FTAway HT FT
251Bristol CityIpswich Town0106-Aug-1103LLLLWWWWLast 3 Games3
3374Bristol CityPortsmouth0020-Aug-1100DDDDDDDDPWDLFAMinMax
4626Bristol CityBrighton & Hove0010-Sep-1101DLDLDWDWBristol City10330123562103
5878Bristol CityHull City0024-Sep-1111DDDDDDDD87
61039Bristol CityReading1027-Sep-1123WLWLLWLW62
Sheet1
 

kingspur06

New Member
Joined
Apr 24, 2007
Messages
28
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,964
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Apr 24, 2007
Messages
28

ADVERTISEMENT

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

Well-known Member
Joined
Jul 10, 2006
Messages
7,964
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
If you copied the formulas without editing, do you have named ranges called Home, Ref, FTHome, FTAway?
 

kingspur06

New Member
Joined
Apr 24, 2007
Messages
28

ADVERTISEMENT

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

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,964
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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

Well-known Member
Joined
Oct 23, 2009
Messages
1,812
Office Version
  1. 2010
Platform
  1. Windows
Hi Mark,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
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:p> </o:p>
Click in any cell within that block.
Press the “Ctrl” key and the “*” key.
<o:p> </o:p>
That will highlight the block of results.
<o:p> </o:p>
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:p> </o:p>
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:p> </o:p>
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:p> </o:p>
Hope this helps you.
 

Forum statistics

Threads
1,147,477
Messages
5,741,362
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top