Instructables

Problems with Excel formula? (semi-related to previous questions)

Hi all,

i've got a problem with a formula in my excel spreadsheet that i'm using to get the scores for NFL teams 1978 - Current

i have the data ok but the problem is with matching the the specific games.

i've CONCATENATED the data to provide unique data points also it's in two columns (Home & Visitor) in the format of

YearTeamW/L/DScore     eg. 2012TitansW35
in the VERY unlikely event (and i've check a random selection) that the team has 2 wins at home with the same score this could cause an error.

here is the formula i'm using: (version ~8000zzzzz lol) (all one formula, just added a line break to make it fit)
=(IF(MATCH($A$3&$B$2&"w*",Home_Team,0)=MATCH($A$3&$C$2&"l*",Visitor,0),INDEX(Visitor,MATCH($A$3&$C$2&"l*",Visitor,0))
,IF(MATCH($A$3&$B$2&"d*",Home_Team,0)=MATCH($A$3&$C$2&"d*",Visitor,0),INDEX(Visitor,MATCH($A$3&$C$2&"d*",Visitor,0)))))

Here's a link to the folder with the spreadsheet. (NFL Stuf (test).xlsx)

Sheet: Past Season Summary - what is says. eventually where the formula is going.
Sheet: nfl2011lines - 92   - the data (actually 1978 - 2012 but i named it before i added all the data)
Sheet: TESTING - my "sandbox" so i don't [Censored] the main sheets


Semi-Related Question1
Semi-Related Question2

furby (author) 1 year ago
i have a MAJOR question that will effect the function of my Spreadsheet.
does every team play every team each season?


i got it mostly working.
i've made another column that concatenates the visitor and home in the format of 1978BengalsL12@197849ersW28

my only problem/ limitation/ issue is that for some years there are blank spots.
i understand that some teams (Cleveland Browns, Tennessee Oilers, Houston Texans) either didn't exist or had franchise problems and didn't play that year.
BUT there are LAGRE gaps or no data (i can't believe that there hasn't been a Vikings @ Patriots for 35 years)

i will post an updated copy to the google drive folder in the original post.


for the Sheet:Home+Away win loss Matrix. Black squares = 0.
furby (author)  furby1 year ago
i've figured out why there are blank spots now

NFL Scheduling Current formula

but it doesn't explain the Vikings @ Patriots 35 year "dead zone"
Jayefuu1 year ago
What's the result you're trying to get? All of the games where the home team won?
furby (author)  Jayefuu1 year ago
i'm trying to get the result of the match up between two teams BUT only if they played each other
eg,
Cardinals @ 49ers will give me
2012Arizona CardinalsL13 2012San Francisco 49ersW27

but the only game it's working for is
rams @ 49ers on 11/11/2012 (the first draw in 8 years)