Formula Help required
 

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

Formula Help required
 

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

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

Good Luck

DB :)




wrote in message
ups.com...
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

Formula Help required
 

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

-----------------------------------------
in ur sheet input this fx.:
=Predicted(B3:C100,"Patriots")
-----------------------------------------



" skrev:

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