Separating Home and Away Results from an Overall Set of Results

Hi,

I do bits and pieces with Sports Data and after discussing something with a friend I am looking to re-work how I look at certain bits of data. To do this I am looking at separating home and away results from an overall set of results (in the past I just did a VLOOKUP and did some least squares regression to generate ratings based on Margin of Victory).

What I am looking to do is to get values from Home and Away Goal Difference for Individual Teams and Overall Home Goal Difference for all teams.

I have made an edit of my 2009/2010 English Premier League Data (I just picked a random year) and have highlighted columns C-F (Home Team/Away Team/Home Team Full Time Goals/Away Team Full Time Goals).

I am thinking that it may be possible to do with a VLOOKUP and some IF functions (e.g. if I had a Goal Difference column for each individual match and perhaps an IF function).

I'd be grateful for some input on this as I have a few ideas but would appreciate a nudge in the right direction.

Thanks in advance,

09_10_Results_EPL_Edit.xls

Hi,

Thanks for the info. In short I am not looking to change where I got the data from I am just looking to separate it into home and away results e.g. from another source:

http://soccernet.espn.go.com/tables/...eague?cc=5739#

This splits it into Overall/Home and Away. I was hoping to adjust my existing data set by applying formulas to get the same result essentially.

I hope this helps?

Thanks Dili, that's spot on for the Home Results. Now I just need to get the Away Data - the way I'm seeing it (apologies I've never done Pivot Tables before - have done Data Tables etc.), is if you had two tables perhaps one for home and one away you could then probably sum the two together to generate an overall one by formula if that makes sense?

Incidentally where do you set up Pivot Tables from?

Originally Posted by Spencer101

So like this (see attachment)? Only more indepth??

Hi Spencer - sorry I couldn't check this, I only have 2004 so I can't do xlsx's.

Originally Posted by Spencer101

Sorry about that.... try this version.

Nice looks virtually spot on and I can understand the formulas used (and why). It just looks like the away results need tweaking as the Wins and Losses, and the Goals For and Goals Against are the wrong way round (if that makes sense - checking against the link above).

Originally Posted by Spencer101

Are you OK tweaking it yourself??

Yeah I think I should be fine - I just want to look at it to make sure I 100% get what I'm doing (I can see what needs to be switched I just want to make sure I agree with why I'm switching it - if that makes sense).

Originally Posted by micope21

Hi mrvp.

I'm out at the moment and I'm using my mobile at the minutes

If you click on micope21 then click on Visit Homepage.

This will go to my website.

Then click on League table.

If that what you want? Then I can sent you copy of workbook later in the evening or first thing tomorrow morning.

Cheers

Hi Micope,

Thanks for the info - I think I'm fine though with some of the ideas others have already come up with. I like Spencer's idea for being the most tidy (I may have misunderstood it but it seems you don't need to have to adjust the cells it is looking up if you are continuously adding data and it also has the more basic formula). Marcol's idea is clever as well as it uses similar formula to that streak table you sorted out for me but looks a bit more involved. I'd been meaning to e-mail you just to say thanks again for that advice it was really handy.

Cheers