Excel Formulas for Sports Simulation

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
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

Well-known Member
Joined
Jul 29, 2002
Messages
501
Harvey said:
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

Well-known Member
Joined
Nov 18, 2004
Messages
953
nice site! (y)

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. :biggrin: Do you have the stats in a sheet?
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
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

Well-known Member
Joined
Nov 18, 2004
Messages
953
I made some example program for you. This are the sheets:
teams.xls
ABCDE
1RankNameSkillTotalscoreGamesPlayed
28H257915510
31A241615510
42B9277510
56F76411010
65E5487010
74D54812510
83C10910010
97G1094010
Rankings
teams.xls
ABCD
1teamAteamBscoreAscoreB
2AB173
3CD1114
4EF38
5GH516
6AC149
7BD1211
8EG113
9FH1415
Games
teams.xls
ABCD
1TeamATeamBScoreAScoreB
2AH1516
3CC1010
4HD5612
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:

Code:
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. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

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