Excel Formula - where to start with this? 1

[MaffewW]

Hi folks

I have a spreadsheet of football data containing loads of info. The bit I am currently working on is doing some calculations based on a teams last x games (lets say x will be 6)

Its ordered by calldate,hometeam and if I can it get it doing what I want then it will have a division column at the front for the whole league

here is my data structure

Date HomeTeam AwayTeam FTHG FTAG
29/03/2006 Man United West Ham 1 0
01/04/2006 Arsenal Aston Villa 5 0
01/04/2006 Birmingham Chelsea 0 0
01/04/2006 Bolton Man United 1 2
01/04/2006 Everton Sunderland 2 2
01/04/2006 Fulham Portsmouth 1 3
01/04/2006 Newcastle Tottenham 3 1
01/04/2006 West Brom Liverpool 0 2
02/04/2006 Man City Middlesboro 0 1
02/04/2006 West Ham Charlton 0 0
03/04/2006 Blackburn Wigan 1 1
04/04/2006 Birmingham Bolton 1 0
08/04/2006 Charlton Everton 0 0
08/04/2006 Portsmouth Blackburn 2 2
08/04/2006 Tottenham Man City 2 1
08/04/2006 Wigan Birmingham 1 1
09/04/2006 Aston Villa West Brom 0 0
09/04/2006 Chelsea West Ham 4 1
09/04/2006 Liverpool Bolton 1 0
09/04/2006 Man United Arsenal 2 0
09/04/2006 Middlesboro Newcastle 1 2



To get me started I need to add 2 columns eg , HomeTeam6Goals and AwayTeam6Goals which will calculate both the Home teams and Away teams goals scored in the previous 6 games. Maybe I need to do it in a database instead I can query. I want to try and find a formulae solution if possible and not change the structure of the data though not sure where to start, can I do it without VBA?

thanks for any pointers

Matt

Brighton, UK

 

[MaffewW]

PS obviously the previous 6 games can have the teams I am looking up in either HomeTeam or AwayTeam column

Matt

Brighton, UK

 

[MaffewW]

I guess this is a non starter, I can import the data into sql server which I know a bit more about and do it that way, though wanted it to be spreadsheet based if poss

Matt

Brighton, UK

 

[xlbo]

This would be very complicated in a formula. Pretty easy in VBA though....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MaffewW]

ok cheers knew it'd be quite complicated dont know any vba though

the idea is to try and gauge a figure for current form based on goals scored + against in the last 6 games (+some other factors) though this is the tricky bit. I could do it in a time frame easily enough this way I think eg goals scored/against in the last 42 days at 1 game per week though I couldnt guarantee the 2 teams would have both always played exactly the same amount of games.



Matt

Brighton, UK

 

[xlbo]

yeh - prob is that you won't know where to look for the last 6 games in a formula. In VBA, you could easily iterate across both columns using the FIND function to get the data and then sum it up.

If your Home Team is in col G & Away Team in col H with HomeGoals in I & AwayGoals in J then

Function TotalGoals(TeamName as string, NumGames as Integer)
Dim lRow as long, i as long, AggrGames as integer, AggrGoals as integer

Const HomeTeamCol = "G"
Const AwayTeamCol = "H"
Const HomeTeamGoals = "I"
Const AwayTeamGoals = "J"

Application.Volatile

AggrGames = 0
AggrGoals = 0
lRow = cells(65536,HomeTeamCol).end(xlup).row

  For i = lRow to 2 step -1
    if cells(i,HomeTeamCol).text = TeamName then
       'Team was at home - get home goals
        AggrGoals = AggrGoals + cells(i,HomeTeamGoals).value
        AggrGames = AggrGames + 1
    else if cells(i,AwayTeamCol).text = TeamName then
       'Team was at Away - get away goals
        AggrGoals = AggrGoals + cells(i,AwayTeamGoals).value
        AggrGames = AggrGames + 1
    end if
    
    If AggrGames = NumGames then exit for
    End If
  Next i
TotalGoals = AggrGoals
End Function

If you right click on your worksheet and choose "View Code", then copy / paste the code above in there, it should work by creating a formula like:
=TotalGoals(G2,6)

where G2 holds a team name. Amend the constants at the start to make it relevant to your setup....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MaffewW]

wow thanks for that, cant get it to work though
(I removed the space on the "else if" as I had compile error) and changed the contacts to mine (CDEF) though I just get #NAME? in the end column

Matt

Brighton, UK

 

[xlbo]

apologies - this code should be placed in a standard module as opposed to the sheet one. When in the VBE, follow Insert>Module and paste the following there:

Function TotalGoals(TeamName As String, NumGames As Integer)
Dim lRow As Long, i As Long, AggrGames As Integer, AggrGoals As Integer

Const HomeTeamCol = "C"
Const AwayTeamCol = "D"
Const HomeTeamGoals = "E"
Const AwayTeamGoals = "F"

Application.Volatile

AggrGames = 0
AggrGoals = 0
lRow = Cells(65536, HomeTeamCol).End(xlUp).Row

  For i = lRow To 2 Step -1
    If Cells(i, HomeTeamCol).Text = TeamName Then
       'Team was at home - get home goals
        AggrGoals = AggrGoals + Cells(i, HomeTeamGoals).Value
        AggrGames = AggrGames + 1
    ElseIf Cells(i, AwayTeamCol).Text = TeamName Then
       'Team was at Away - get away goals
        AggrGoals = AggrGoals + Cells(i, AwayTeamGoals).Value
        AggrGames = AggrGames + 1
    End If
    
    If AggrGames = NumGames Then
        Exit For
    End If
  Next i
TotalGoals = AggrGoals
End Function

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MaffewW]

cool its calculating now though I think its giving me the first 6 games of season not the previous 6. I tried ordering my list by date descending though it didnt calculate the proper figure

thanks for your help on this

Matt

Brighton, UK

 

[xlbo]

It should work from the bopttom of your dataset to the top so if you sort by Date ascending, it should work ok

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MaffewW]

no mate unless I'm missing something, somethings afoot. Sorry, was wrong before it wasnt counting from the top.

Here is the bottom section of the data, I'm using Birmingham as the test record and the function is giving me 2 instead of 9.

cheers

A,B,C,D,E,F
div,date,ht,at,hg,ag
E0,22/02/2006,Newcastle,Charlton,0,0
E0,25/02/2006,Birmingham,Sunderland,1,0
E0,25/02/2006,Blackburn,Arsenal,1,0
E0,25/02/2006,Charlton,Aston Villa,0,0
E0,25/02/2006,Chelsea,Portsmouth,2,0
E0,25/02/2006,Newcastle,Everton,2,0
E0,26/02/2006,Bolton,Fulham,2,1
E0,26/02/2006,Liverpool,Man City,1,0
E0,26/02/2006,West Brom,Middlesboro,0,2
E0,04/03/2006,Aston Villa,Portsmouth,1,0
E0,04/03/2006,Fulham,Arsenal,0,4
E0,04/03/2006,Liverpool,Charlton,0,0
E0,04/03/2006,Middlesboro,Birmingham,1,0
E0,04/03/2006,Newcastle,Bolton,3,1
E0,04/03/2006,West Brom,Chelsea,1,2
E0,04/03/2006,West Ham,Everton,2,2
E0,05/03/2006,Man City,Sunderland,2,1
E0,05/03/2006,Tottenham,Blackburn,3,2
E0,06/03/2006,Wigan,Man United,1,2
E0,11/03/2006,Birmingham,West Brom,1,1
E0,11/03/2006,Blackburn,Aston Villa,2,0
E0,11/03/2006,Bolton,West Ham,4,1
E0,11/03/2006,Chelsea,Tottenham,2,1
E0,11/03/2006,Everton,Fulham,3,1
E0,11/03/2006,Portsmouth,Man City,2,1
E0,11/03/2006,Sunderland,Wigan,0,1
E0,12/03/2006,Arsenal,Liverpool,2,1
E0,12/03/2006,Charlton,Middlesboro,2,1
E0,12/03/2006,Man United,Newcastle,2,0
E0,15/03/2006,Liverpool,Fulham,5,1
E0,18/03/2006,Arsenal,Charlton,3,0
E0,18/03/2006,Birmingham,Tottenham,0,2
E0,18/03/2006,Blackburn,Middlesboro,3,2
E0,18/03/2006,Bolton,Sunderland,2,0
E0,18/03/2006,Everton,Aston Villa,4,1
E0,18/03/2006,Man City,Wigan,0,1
E0,18/03/2006,West Brom,Man United,1,2
E0,18/03/2006,West Ham,Portsmouth,2,4
E0,19/03/2006,Fulham,Chelsea,1,0
E0,19/03/2006,Newcastle,Liverpool,1,3
E0,25/03/2006,Aston Villa,Fulham,0,0
E0,25/03/2006,Chelsea,Man City,2,0
E0,25/03/2006,Liverpool,Everton,3,1
E0,25/03/2006,Sunderland,Blackburn,0,1
E0,25/03/2006,Wigan,West Ham,1,2
E0,26/03/2006,Charlton,Newcastle,3,1
E0,26/03/2006,Man United,Birmingham,3,0
E0,26/03/2006,Middlesboro,Bolton,4,3
E0,27/03/2006,Tottenham,West Brom,2,1
E0,29/03/2006,Man United,West Ham,1,0
E0,01/04/2006,Arsenal,Aston Villa,5,0
E0,01/04/2006,Birmingham,Chelsea,0,0
E0,01/04/2006,Bolton,Man United,1,2
E0,01/04/2006,Everton,Sunderland,2,2
E0,01/04/2006,Fulham,Portsmouth,1,3
E0,01/04/2006,Newcastle,Tottenham,3,1
E0,01/04/2006,West Brom,Liverpool,0,2
E0,02/04/2006,Man City,Middlesboro,0,1
E0,02/04/2006,West Ham,Charlton,0,0
E0,03/04/2006,Blackburn,Wigan,1,1
E0,04/04/2006,Birmingham,Bolton,1,0

Matt

Brighton, UK

 

[xlbo]

Are you sure ???

Birmingham's last 6 games:

vs Middlesborough - scored 0
vs West Brom - scored 1
vs Tottenham - scored 0
vs Man United - scored 0
vs Chelsea - scored 0
vs Bolton - scored 1

9 would be the total goals scored in the 6 games by all clubs that played Birmingham but that's not how I interpreted your request as I really don't see what that would get you - I thought you wanted the no. of goals scored by the named team in the last x matches rather than the total no. of goals scored in those 6 matches - it can be quite easily fixed but I don't see the benefit of the latter result over the former.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MaffewW]

doh

you read my question fine and it works perfect!

Friday lunchtime beer interference!!

thanks for you help mate when I turn it into a money making forecast system I shall remember you

Matt

Brighton, UK

 

[xlbo]

no worries

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question