variable with negative correlation isn't negatively correlated

[luegofuego]

In soccer, corners are negatively correlated with scoring. This is because:

1) a shot resulting in a corner means it DOESN'T result in a goal
2) teams that are behind produces a much higher amount of corners due to desperate attacking

This is puzzling to me, since a team producing a lot of corners, by definition, are producing a lot of scoring chances. A team that has produced many corners in the past, should produce more goals in the future.

So I wanted to test if PAST corners are positively correlated with FUTURE goals. What's the best way to do this? Will I need to add a new variable for each game, containing "corner average in every game but the current one" and then run a regression on that?

Are there better ways? And if not, is there a relatively simple way to extract that variable in excel? All I could come up with was a fuckton of nested IF-functions going by date etc. Being less than expert at excel, It gave me a headache.

[mycon]

This is puzzling to me, since a team producing a lot of corners, by definition, are producing a lot of scoring chances. A team that has produced many corners in the past, should produce more goals in the future.

I think that is probably a fallacy. Many corners don't necessarily translate into many goal scoring opportunities. Also the efficiency both offensively and defensively can vary greatly, depending on training focus. It very much depends on who against whom.

And it's also not uncommon for teams to not score after corners for a long time, I know that because I got interested in the subject when my favorite team couldn't score after a corner for over a year (!).

My guess is that just the number of corners on their own mean very little if anything at all, because it doesn't take into account how good a team is at scoring from corners and defending them. It's also a factor that can change very rapidly within a team just because there's a new team manager.

[Justin7]

luego,

What correlation did you find between corners and scoring?

If you have play-by-play data, I would look at corners, and compare them to goals scored within 1 minute. I thought that roughly 1/6 corners were converted to goals, but don't quote me on that.

[luegofuego]

J7,

For the home team, B was -0.005 for corners in a linear regression.

What you're saying is, if I wanted to build some kind of soccer model, you would first determine how many corners end up being goals, and then adjust the score by that number for each corner?

[That Foreign Guy]

I know that because I got interested in the subject when my favorite team couldn't score after a corner for over a year (!).

Do you support Arsenal?

Luego - It sounds like a sumifs could do it:

http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx

=SUMIFS(corners column, team column, = team , date column , < today)

[luegofuego]

Yeah, I'm just having problems with an Excel date formating bug.

[Justin7]

J7,

For the home team, B was -0.005 for corners in a linear regression.

What you're saying is, if I wanted to build some kind of soccer model, you would first determine how many corners end up being goals, and then adjust the score by that number for each corner?

I'm not saying anything... I have never been very successful at soccer. I could beat small leagues on totals, but nothing with real liquidity.