Formula Help required

[johnnagle]

I am trying to complete a task in excel without the use of macros if
possible.

I have a table of football results like this

Week Home Team Away Team Home Score Away Score Predicted Home
Pred. Away

3 Patriots Dolphins ? ?
2 Patriots Jets 22 10
2 Chargers Bills 31 23
2 Colts Falcons 10 12
1 Bills Patriots 12 14

What I need to do is add the last 4 scores of a team, divide by 4 and
have this display as the predicted score for that team on the row for
the upcoming week.

I am not sure how I can get excel to find the last 4 scores of a team
regardless of whether they played at home or away.

Any help would be much appreciated. Thanks

 

[DB]

cant help you at all mate.....sorry...

looks to me like you are trying to beat the bookies,lol

Good Luck

DB

 

[Guest]

Tryed with a formula - no luck so maby a function


Function Predicted(myRange As Range, Team)

Dim t(30), col(30), score(30)
Application.Volatile

For Each c In myRange
If c = Team Then
t(nbr) = Cells(c.Row, 1)
col(nbr) = c.Column
score(nbr) = c.Offset(0, 2)
nbr = nbr + 1
End If
Next

For n = 0 To UBound(t)
For i = n + 1 To UBound(t)
If t(i) > t(n) Then
x = t(n): t(n) = t(i): t(i) = x
x = col(n): col(n) = col(i): col(i) = x
x = score(n): score(n) = score(i): score(i) = x
End If
Next
Next

Predicted = score(0) + score(1) + score(2) + score(3) / 4

End Function