144Views4Replies

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

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

Tags:

## Discussions

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

I think this will help you S+/- is score differential between Home - Opponent
And the other side S+/- is Away - Opponent
Remember Away is the Home team visiting the opponent team

Home Score Opponent Score S+/- W L T / Opponet score Away Score S+/- W L T
If(H2>0,1," ")=W
If(H2<0,1," ")= L
If(H2=0,1," ")= T

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.

What's the result you're trying to get? All of the games where the home team won?

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)