Excel Formulas for Sports Simulation

[gottimd]

I am part of a Football Sports Simulated league, and I would like to develop some sort of excel formula(s) in regards to predicting the outcome of games or generating spreads for our league homepage. I have been searching everywhere for examples of how to do this, but the only accurate way I have seen on the net is a very old code. I have numerous amounts of stats that I could include, so if anyone can point me in the right direction on how to develop something through excel that would calculate this it would be greatly appreciated.

Thanks for your help in advance.

 

[Harvey]

You have to give us more detailed information. Do you want to predict the scores in a game or just the winner? What kinds of stats do you have, and in what format? I also think that since you want to include this data on your webpage, you might want to take a look at php forums instead.

 

[gottimd]

You have to give us more detailed information. Do you want to predict the scores in a game or just the winner? What kinds of stats do you have, and in what format? I also think that since you want to include this data on your webpage, you might want to take a look at php forums instead.

I want to predict the scores, which in essencewould give me the spreads of each simulated game. I can get any sort of stat, take a look at our league website (http://www.modernbaseball.org/FOF/index.html) I have created other reports through excel and have macro's that save them as HTML pages, and I export to the commissioner for him to put on the site, and I wanted to add another report for game spreads/previews. I am not familiar with PHP so I didn't even look over there.

 

[Harvey]

nice site!

I think the best way to predict the scores in a match of two teams is to take the average of what both teams usually score and multiply it with the "skill" value of each team, where the skill would be the overall strength of the team, which increases by beating other teams, but I'm not sure if I'm the right person to predict football scores, because I do not know much about football. Do you have the stats in a sheet?

 

[gottimd]

I have multiple excel sheets. On the menu on the left you can see a few reports, the ones that look like they are done in excel are the ones I developed, which takes statistics and code and parses and filters and sorts.

 

[Harvey]

I made some example program for you. This are the sheets:

teams.xls
	A	B	C	D	E
1	Rank	Name	Skill	Totalscore	GamesPlayed
2	8	H	2579	155	10
3	1	A	2416	155	10
4	2	B	927	75	10
5	6	F	764	110	10
6	5	E	548	70	10
7	4	D	548	125	10
8	3	C	109	100	10
9	7	G	109	40	10
Rankings

teams.xls
	A	B	C	D
1	teamA	teamB	scoreA	scoreB
2	A	B	17	3
3	C	D	11	14
4	E	F	3	8
5	G	H	5	16
6	A	C	14	9
7	B	D	12	11
8	E	G	11	3
9	F	H	14	15
Games

teams.xls
	A	B	C	D
1	TeamA	TeamB	ScoreA	ScoreB
2	A	H	15	16
3	C	C	10	10
4	H	D	56	12
Predict

If you put your data in this order (stats in sheet2, team names in sheet1, matches to predict in sheet3)

And put this code into your workbook:

Sub CalculateSkills()

    Dim cell As Range
    Dim mRow As Integer
    Dim teamA As String
    Dim teamB As String
    Dim teamAscore As String
    Dim teamBscore As String
    Dim teamAskill As String
    Dim teamBskill As String
    Dim teamAwins As Boolean
    Dim teamAposition As Integer
    Dim teamBposition As Integer
    Dim skilldifference As Integer
    
    For Each cell In Range("Games!A2:A9")
        mRow = cell.Row
        teamA = Sheets("Games").Cells(mRow, 1)
        teamB = Sheets("Games").Cells(mRow, 2)
        teamAscore = Sheets("Games").Cells(mRow, 3)
        teamBscore = Sheets("Games").Cells(mRow, 4)
        teamAwins = Val(teamAscore) > Val(teamBscore)
        teamAposition = Range("Rankings!B2:B9").Find(teamA).Row
        teamBposition = Range("Rankings!B2:B9").Find(teamB).Row
        teamAskill = Sheets("Rankings").Cells(teamAposition, 3)
        teamBskill = Sheets("Rankings").Cells(teamBposition, 3)
        If teamAwins Then
            skilldifference = Int(teamBskill / 5)
            teamAskill = teamAskill + skilldifference
            teamBskill = teamBskill - skilldifference
        Else
            skilldifference = Int(teamAskill / 5)
            teamAskill = teamAskill - skilldifference
            teamBskill = teamBskill + skilldifference
        End If
        Sheets("Rankings").Cells(teamAposition, 3) = teamAskill
        Sheets("Rankings").Cells(teamBposition, 3) = teamBskill
        Sheets("Rankings").Cells(teamAposition, 4) = _
            Sheets("Rankings").Cells(teamAposition, 4) + teamAscore
        Sheets("Rankings").Cells(teamAposition, 5) = _
            Sheets("Rankings").Cells(teamAposition, 5) + 1
        Sheets("Rankings").Cells(teamBposition, 4) = _
            Sheets("Rankings").Cells(teamBposition, 4) + teamBscore
        Sheets("Rankings").Cells(teamBposition, 5) = _
            Sheets("Rankings").Cells(teamBposition, 5) + 1
    Next cell

    Range("A2:E9").Sort Key1:=Range("C2:C9"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End Sub

Sub PredictScore()

    Dim cell As Range
    Dim mRow As Integer
    Dim teamA As String
    Dim teamB As String
    
    For Each cell In Range("Predict!A2:A4")
        mRow = cell.Row
        teamA = Sheets("Predict").Cells(mRow, 1)
        teamB = Sheets("Predict").Cells(mRow, 2)
        teamAposition = Range("Rankings!B2:B9").Find(teamA).Row
        teamBposition = Range("Rankings!B2:B9").Find(teamB).Row
        teamAskill = Sheets("Rankings").Cells(teamAposition, 3)
        teamBskill = Sheets("Rankings").Cells(teamBposition, 3)
        teamAwins = Val(teamAskill) > Val(teamBskill)
        If teamAwins Then
            teamBscore = Int(Sheets("Rankings").Cells(teamBposition, 4) / _
                Sheets("Rankings").Cells(teamBposition, 5))
            teamAscore = Int(teamBscore * (teamAskill / teamBskill))
        Else
            teamAscore = Int(Sheets("Rankings").Cells(teamAposition, 4) / _
                Sheets("Rankings").Cells(teamAposition, 5))
            teamBscore = Int(teamAscore * (teamBskill / teamAskill))
        End If
        Sheets("Predict").Cells(mRow, 3) = teamAscore
        Sheets("Predict").Cells(mRow, 4) = teamBscore
    Next cell

End Sub

If you run the calculateskills sub once, and thereafter the predictscore, your last sheet will show the predicted scores. Im not sure if my prediction is completely right, but maybe you can alter it a bit to make it useful.

P.S.
The sheets have to be named "Rankings", "Games" and "Predict"
If you run the calculateskills sub you have to empty the sheet after, because otherwise matches will be count double. You also have to give every team the same starting skill, before you run any code (ie 100, 1000 or 10000, depending on how accurate you want it to behave)

Success with your website.