formula for strength of schedule?

roasthawg,

Attached is a modified version of your posted workbook.

First, I simplified the formulas in column G and H. In cell G2 and copied over and down to H10 is this regular formula:

=IF(COUNTIF($B$1:$C1,B2)>0,AVERAGEIF($B$1:$C1,B2,$D$1:$E1),"")

Then I addressed your actual question. In cell J2 and copied over and down to cell K10 is this regular formula:

=IF(COUNTIF($B$1:$C1,B2)=0,"",(SUMIF($B$1:$B1,B2,$E$1:$E1)+SUMIF($C$1:$C1,B2,$D$1:$D1))/COUNTIF($B$1:$C1,B2))

Is that what you're looking for?

I like the simplification of the first formula... thanks!

As far as what I'm after however, still not there. The formula you gave

=IF(COUNTIF($B$1:$C1,B2)=0,"",(SUMIF($B$1:$B1,B2,$E$1:$E1)+SUMIF($C$1:$C1,B2,$D$1:$D1))/COUNTIF($B$1:$C1,B2))

looks like it's calculating the team's defense. I need the team's opponents AVERAGES as of the date of the current game.

For example... on Jan 4 team "A" has played three games, one each against teams "B", "C", and "D". I want to know the AVERAGE of team's "B", "C", and "D" pts scored and pts against for all games up thru Jan 3. Make sense? If not, let me know. Thanks for the help!

roasthawg,

The formula I provided gave correct results based on what you entered manually for team "b". When I checked it manually against team "a" it also provided correct results. I know the formula doesn't use Average() but it does use (Sumif()+Sumif())/Countif() in order to get the average. I had to go this route because of using opposite columns to calculate opponent average.

If it is, in fact, yielding incorrect results, please show me what result is incorrect, what it should be instead, and how you arrived at that answer.

Sorry, I wan't capitalizing AVERAGE as commentary on your use of sumifs instead of averageifs... only to try to clarify what I am after.

Ok, here's an example of an incorrect result using your formula... in cell B8 we have team "C". The date of this game is Jan 3. Up until this point of the season team "C" has completed games against teams "E" and team "F". Prior to the start of Jan 3rd's games team "E" averaged 3 as evidenced in cell G9 and team "F" averaged 3.5 as evidenced in cell H10. The average of these two numbers is the result I'm after... 3.25 in this case. Hope that helps... thanks for taking time on this!

roasthawg,

Ah, ok. That makes things much more complicated. In order to use only formulas, I had to use the MCONCAT function which is provided by the Morefunc add-in.

Attached is version 2. Column M is a helper column. In cell M2 and copied down is this formula:

=LOOKUP(2,1/($A$1:$A1<>$A2),ROW(INDIRECT("1:"&ROWS($A$1:$A1))))

Then, in cell J2 and copied down to J10, then copied to cells K2:K10 is this array formula:

=IF(COUNTIF($B$1:INDEX($C$1:$C1,$M2),$B2)=0,"",SUMPRODUCT(SUMIF($B$1:INDEX($C$1:$C1,$M2),INDEX(TRIM(MID(SUBSTITUTE(TRIM(MCONCAT(IF($B$1:INDEX($B$1:$B1,$M2)=B2,$C$1:INDEX($C$1:$C1,$M2),IF($C$1:INDEX($C$1:$C1,$M2)=B2,$B$1:INDEX($B$1:$B1,$M2),""))," "))," ",REPT(" ",99)),99*(ROW(INDIRECT("1:"&COUNTIF($B$1:INDEX($C$1:$C1,$M2),B2)))-1)+1,99)),),$D$1:$E1))/SUM(COUNTIF($B$1:INDEX($C$1:$C1,$M2),INDEX(TRIM(MID(SUBSTITUTE(TRIM(MCONCAT(IF($B$1:INDEX($B$1:$B1,$M2)=B2,$C$1:INDEX($C$1:$C1,$M2),IF($C$1:INDEX($C$1:$C1,$M2)=B2,$B$1:INDEX($B$1:$B1,$M2),""))," "))," ",REPT(" ",99)),99*(ROW(INDIRECT("1:"&COUNTIF($B$1:INDEX($C$1:$C1,$M2),B2)))-1)+1,99)),))))

It is very long because it is very complex. You needed to get teams that had played against the current row's teams prior to the current row's date, and then average all of their scores together. This formula does that and is the only way I can think of to do so without resorting to VBA (a UDF or macro). It does however provide the correct results for team "b" and the corect results for team "c" and does so using the logic described in your most recent post.

You can hide column M if you want.

Its in the "Excel Programming / VBA / Macros" subforum, that's true. Generally speaking though it is better to avoid the use of VBA unless absolutely necessary. User's posted workbook is a .xlsx file which by definition has no macros, and there were already array formulas in it, so I just kept with formulas. If OP would like a VBA solution I can provide that as well, but it didn't (and still doesn't) seem to be necessary.

A macro or UDF would certainly make it cleaner, and depending on your level of expertise with VBA it could certainly be easier. But then anytime you opened the workbook you'd be prompted to enable macros, or if you distributed the workbook others would get the prompt and would be very unlikely to know how to interact with macros at all.

Still, if you'd like a VBA solution, let me know.

roasthawg,

Attached is v3. It contains a button that will get calculate the opponent average. This is the macro the button is assigned to:

Sub tgr()
    
    Dim rngTeams As Range
    Dim rngAway As Range
    Dim rngHome As Range
    Dim rngScores As Range
    Dim strOpponents As String
    Dim arrTeam(1 To 2) As String
    Dim arrData() As Double
    Dim DataIndex As Long
    Dim rIndex As Long
    Dim lRow As Long
    Dim i As Long
    
    Range("J2:K" & Rows.Count).ClearContents
    Set rngTeams = Range("B1:C1")
    Set rngScores = Range("D1:E1")
    ReDim arrData(1 To Cells(Rows.Count, "A").End(xlUp).Row, 1 To 2)
    
    For rIndex = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        DataIndex = DataIndex + 1
        arrTeam(1) = Cells(rIndex, "B").Value
        arrTeam(2) = Cells(rIndex, "C").Value
        lRow = Evaluate("lookup(2,1/(A1:A" & rIndex - 1 & "<>" & Cells(rIndex, "A").Value2 & "),row(1:" & rIndex - 1 & "))")
        Set rngTeams = rngTeams.Resize(lRow)
        Set rngAway = rngTeams.Resize(, 1)
        Set rngHome = rngTeams.Offset(, 1).Resize(, 1)
        Set rngScores = rngScores.Resize(lRow)
        
        For i = 1 To 2
            If WorksheetFunction.CountIf(rngTeams, arrTeam(i)) > 0 Then
                strOpponents = Join(Filter(Application.Transpose(Evaluate("If(" & rngAway.Address & "=""" & arrTeam(i) & """," & rngHome.Address & ",If(" & rngHome.Address & "=""" & arrTeam(i) & """," & rngAway.Address & "))")), False, False), """,""")
                arrData(DataIndex, i) = Evaluate("Average(Index(AverageIf(" & rngTeams.Address & ",{""" & strOpponents & """}," & rngScores.Address & "),))")
            End If
        Next i
    Next rIndex
    
    Range("J2:K2").Resize(DataIndex).Value = arrData
    
    Set rngTeams = Nothing
    Set rngAway = Nothing
    Set rngHome = Nothing
    Set rngScores = Nothing
    Erase arrTeam
    Erase arrData
    
End Sub