Right, this is quite hard to describe, but I'll have a go.
I am trying to find a satisfactory way of making a football (soccer) ranking
spreadsheet using Excel without having to resort to too much programming. I
have a large collection of international results (over 32,000 and counting)
which I use to define the current ranking system. There is basically a
formula for each match that calculates the points gained for each match based
on difference in rating between two teams and the match result.

The problem is, as the rating changes for each team after each match, how
can I somehow store this new rating without having to put a formula in each
row? Some teams have only played a handful of games out of the 32,000, so
how can I only use the number of cells necessary for each team, rather than
having 32,000 rows * 310 columns (there are 310 teams), as my computer does
not have the resources to display such a vast number of formulae, and ceases
to operate.

At the moment, I am basically putting a formula into each team's column
saying "If the team playing in row2 is team x, then add team x's points from
this game to team x's rating points from the row above, else, use team x's
ratings points from the row above unchanged". So, basically, there are 308
redundant formulae in each row, as only 2 teams can play in any one match.
Any help would be greatly appreciated.

I'm having trouble visualizing your data, but I would think you could
use some type of lookup to do it. For example, if your data were in
descending date sequence, the previous rankings for each team would be
below your current match. You could just use a lookup on the
remaining rows of the table to find those two rankings, then apply
your formula to get a new ranking.

Have you tried a Google search? I would think this type of matchup
process would have been done before...

Mcruic wrote:
Right, this is quite hard to describe, but I'll have a go.
I am trying to find a satisfactory way of making a football (soccer) ranking
spreadsheet using Excel without having to resort to too much programming. I
have a large collection of international results (over 32,000 and counting)
which I use to define the current ranking system. There is basically a
formula for each match that calculates the points gained for each match based
on difference in rating between two teams and the match result.

The problem is, as the rating changes for each team after each match, how
can I somehow store this new rating without having to put a formula in each
row? Some teams have only played a handful of games out of the 32,000, so
how can I only use the number of cells necessary for each team, rather than
having 32,000 rows * 310 columns (there are 310 teams), as my computer does
not have the resources to display such a vast number of formulae, and ceases
to operate.

At the moment, I am basically putting a formula into each team's column
saying "If the team playing in row2 is team x, then add team x's points from
this game to team x's rating points from the row above, else, use team x's
ratings points from the row above unchanged". So, basically, there are 308
redundant formulae in each row, as only 2 teams can play in any one match.
Any help would be greatly appreciated.

Mcruic,

I love soccer, so this was rather fun for me. Here it goes.

I used only one function (listed below) that does hopefully what you want,
and two named ranges. Since I don't know how you have your rankings etc set
up, I took some liberties with it, but hopefully this will make sense.

I have set up two named ranges, TeamRankings(A2:B4) and TeamPts(C2:D4). In
B2 I entered the formula (=VLOOKUP(A2,TeamPts,2)), then copied down to
remaining cells. This way, you don't have to mess with this part again.

The teams' current pts are updated by the function, so you may leave empty
or put a 0 if you'd like when setting up. Does not matter.

A B C D
1 team pts team current pts
2 t2 6 t1 0
3 t3 3 t2 6
4 t1 0 t3 3
5
6 game result
7 t1 v t2 1-3
8 t2 v t3 1-5
9 t2 v t1 4-1

To update the rankings simply select the range A7:B9 and run the macro
(ComputeRankings) - you might want to assign this to a shortcut, I used
ctrl+r for this.

The macro updates the current pts and sorts the teams according to pts.

Public Sub ComputeRankings()
Dim matchesRange As range ' selection of all the
matches or a single match
Dim matchIndex As Integer
Dim teamPtsRange As range ' the range corresponding
to the Name: TeamPts
Dim teamCol As New Collection ' a collection of team/pts
pairs
Dim teamIndex As Integer


' To speed things up, let's turn off redrawing of the screen until we
are done
Application.ScreenUpdating = False

' Note: Code makes use of a named range of cells, called TeamPts. This
range will
' include the team name and total pts, in two columns. Put anywhere in
the worksheet and
' hide if you want it to be out of sight.
' A1: Team1 B1: 10
' A2: Team2 B2: 3
' A3: Team3 B3: 14
' ...

' Create the range corresponding to the name: TeamPts
Set teamPtsRange = Names("TeamPts").RefersToRange

' Populate the collection of teams for easy referencing later on,
' when processing match results. Initially, let's set every team's
' total pts to 0, so each time you run the macro, the proper pts will
' be computed, based on all the matches. Easier that way. :)
For teamIndex = 1 To teamPtsRange.Rows.Count
' add the team name as the key and its pts as the value
teamCol.Add teamPtsRange(teamIndex, 2), CStr(teamPtsRange(teamIndex,
1))
teamCol(teamIndex) = 0
Next

' Selection here refers to the cells containing the matches and their
result.
' A1: team1 v team2 B1: '1-2
' ...
' Must select cells before running macro.

Set matchesRange = Selection

' For each match, identify teams, game's results and update
' team total points.
For matchIndex = 1 To matchesRange.Rows.Count
' compute pts based on match results and update team pts.
' use whatever formula you are using now; i'm going to fudge it here.
Dim teams As String ' the teams playing: team1 v team2
Dim goals As String ' the result: 1-2
Dim team1 As String ' team1
Dim team2 As String ' team2
Dim goal1 As Integer ' 1
Dim goal2 As Integer ' 2

teams = matchesRange(matchIndex, 1)
goals = matchesRange(matchIndex, 2)

' parse each team
Dim vPos As Long
vPos = InStr(1, teams, " v ", vbTextCompare)
team1 = Mid(teams, 1, vPos - 1)
team2 = Mid(teams, vPos + 3)

' parse each team's goals
Dim dPos As Long
dPos = InStr(1, goals, "-", vbTextCompare)
goal1 = Mid(goals, 1, dPos - 1)
goal2 = Mid(goals, dPos + 1)

' check who won and award 3pts for winner, 0 for looser, 1 pt each
if tied
' did team1 win?
If goal1 goal2 Then
' team1 won
teamCol(team1) = teamCol(team1) + 3
ElseIf goal2 goal1 Then
' team2 won
teamCol(team2) = teamCol(team2) + 3
Else
' teams tied
teamCol(team1) = teamCol(team1) + 1
teamCol(team2) = teamCol(team2) + 1
End If
Next

' Now sort team rankings
' Note: create a named range, TeamRankings, to include the team and
their points.
' A1: Team1 B1: 10
' A2: Team2 B2: 9
' ...
Dim teamRankingsRange As range

Set teamRankingsRange = Names("TeamRankings").RefersToRange

teamRankingsRange.Sort Key1:=teamRankingsRange(1, 2),
Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

' All done! Turn on screen redrawing
Application.ScreenUpdating = True
End Sub

That's all there is to it and the spreadsheet is rather mimimal, w/out any
formulas other than in TeamRankings. Enter the new match results, select the
range and run the macro.

HTH, and go Manchester Utd. :)

Cheers,
Socratis
"Mcruic" wrote:

Right, this is quite hard to describe, but I'll have a go.
I am trying to find a satisfactory way of making a football (soccer) ranking
spreadsheet using Excel without having to resort to too much programming. I
have a large collection of international results (over 32,000 and counting)
which I use to define the current ranking system. There is basically a
formula for each match that calculates the points gained for each match based
on difference in rating between two teams and the match result.

The problem is, as the rating changes for each team after each match, how
can I somehow store this new rating without having to put a formula in each
row? Some teams have only played a handful of games out of the 32,000, so
how can I only use the number of cells necessary for each team, rather than
having 32,000 rows * 310 columns (there are 310 teams), as my computer does
not have the resources to display such a vast number of formulae, and ceases
to operate.

At the moment, I am basically putting a formula into each team's column
saying "If the team playing in row2 is team x, then add team x's points from
this game to team x's rating points from the row above, else, use team x's
ratings points from the row above unchanged". So, basically, there are 308
redundant formulae in each row, as only 2 teams can play in any one match.
Any help would be greatly appreciated.