Author Options:

excel problem. averaging results of index match? Answered

Hi all (again)

new problem semi related to previous. (check my profile for the ones with NFL in the title)

now the problem is i want to get the average score for each team in each season.
my current formula only gets the first result of the index/match even if i use some of the tricks i've googled.

the data is in the format of
YearVisitingTeam(W/L/D)Score@HomeTeam(W/L/D)Score          ie   201249ersW41@2012PatriotsL34

i use a formula to strip "Year(Visiting/Home)Team(W/L/D)" so i' left with Score on either side of the "@" (that was a [redacted] pain)

using any and all of the tricks i know i get either 0, 27 or an error of some sort.

the only way i've been sorta able to do this is by getting the first home win, first home loss, first away win, first away loss BUT that's only 4 of 16 games each season and not a good representation of the average points for the team in a season.

the sheet in question is AVG point - Reg Season
the source data is 78 - current regular season (2012 season starts @ row 3152)


The forums are retiring in 2021 and are now closed for new topics and comments.

8 years ago

Never mind i figured it out.

=IFERROR((SUMIF(visitscore,G$1&$A2,'78 - current regular season'!$C:$C)/COUNTIF(visitscore,G$1&$A2),"")