Instructables

excel problem. averaging results of index match?

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)

furby (author) 1 year ago
Never mind i figured it out.

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

Pro

Get More Out of Instructables

Already have an Account?

close

PDF Downloads
As a Pro member, you will gain access to download any Instructable in the PDF format. You also have the ability to customize your PDF download.

Upgrade to Pro today!