Need programming help for last 6 matches in excel

[Pepen]

Hi guys. I've been following this forum for quite some time now, and due to an assignment at my university, I'm given the task of trying to make a rating system for 1x2. Sorry if this question is too offtopic, but I'll need the macro to start my system (: I've decided to use available data from the PL at http://football-data.co.uk/englandm.php just to make a template of the final "system".. The only problem so far is, that I'm not exactly a genius when it comes to programming in excel (only done some basic C++, and very little VBA). Is there anybody who can help me with a macro that finds the last 6 matches for both of the involved teams? For example a round 10 match: Man Utd vs Everton Here I'd like a macro that identifies each team and find the last 6 matches they've been involved in, so I can use e.g. the accumulated point/goals or any such thing. Thanks in advance for any help :)

[gloryhunters]

Re: Need programming help for last 6 matches in excel Well I think you need to create several arrays in VBA. Say hometeam, homegoals etc. and a counter Then go to your data sheet and go down the home team column and step down the each column asking if the team is homegoals. if yes then increase your counter by 1 and all your variables you need homegoals(counter) is what is in that row. Do this as you go down the list until you hit a blank cell. Your last 6 matches will be homegoals(counter), homegoals(counter-1), etc. I have been doing this for my spreadbets and so just look at one match at a time and do this within Excel without VBA. Basically you make 2 columns to the left of the data set and have a formula like: =IF(E22=Goals!$C$2,ROUND(A21+1,0),A21+0.00001) this is for line 22. The first bit asks if the team is as you ask for in a different sheet. If yes then add one and round otherwise add a very small bit. On another sheet you can have a row full of numbers (say 1 to 40) and next to each one say: =IF(A6

[Pepen]

Re: Need programming help for last 6 matches in excel I'm feeling pretty dumb right now, as I simply cannot figure out how to apply those functions to my datasheet, mainly because I probably don't understand them correctly. I was trying to PM you, but apparantly that's not an option for me (or I lack the intelligence to figure out how) but I would be happy with some help, as it's getting alittle frustrating trying various things out with no realy progress. I'd like to post a bit of the datasheet in excel format here, but being new to PL I'm not sure how thats done, so the formatting doesn't get completely destroyed.. Just to be sure, those 2 functions you wrote above, will single the 2 teams out in a specific match and then take the previous 6 matches with one of them participating and use data from that match? For example a game between Blackburn and Sunderland; here I'd like to find the previous 6 games where Blackburn was either Home or Away, extract the data from these and then do the same with Sunderland.. Thank you for the little bit of help, I hope I'll eventually get the overall picture so I might stop buggering you :)

[PAW]

Re: Need programming help for last 6 matches in excel There's a recent post by muppet77 that might help http://www.punterslounge.com/forum/f23/excel-help-extracting-recent-data-tables-99506/#post1639947

[Pepen]

Re: Need programming help for last 6 matches in excel Thanks pip for the link, I've just seen it and tried working with it, but apparantly I'm not able to use it for BOTH home and aways, not sure if it's a faulty function for my purpose or if I'm just not good enough at using excel :]

[muppet77]

Re: Need programming help for last 6 matches in excel Yes I only wrote it for "home team home goals for" as an eg. What exactly are you after? There are many combos!

[Pepen]

Re: Need programming help for last 6 matches in excel Hi Muppet. Actually what I need to be alittle specific, is a macro or some function alike the one you made in that excelsheet, that finds both of the teams in any given match's last 6 games (both home and away) and then extracts some information from those matches. E.g points pr game, accumulated points would then be last match points + 2nd last match points etc. I just thought that it was possible to make a macro, that would put the accumulated info for the past 6 games (of both teams) into adjacent cells, to the game in question and adjust this macro for each thing you look for, be it shots on target, goals, points or whatever. I'm trying to make a sort of predictive model, that uses different variables, but in order to ues these i'll have to test if they're relevant, and some of these variables are based on the teams 6 most recent matches, which will then have to be tested using Chi squared or another test, to see if the variable have any influence on the (expected) outcome of any given match. Not sure if I explain myself in any understandable way, english is not my native language so I guess some of my points gets lost in translation :)

[KThom]

Re: Need programming help for last 6 matches in excel Try labelling each game (1 through to 380 for a 20 team league) then by using the "large" and "sumif" functions anything you want to do should be fairly easy to extract.

[Pepen]

Re: Need programming help for last 6 matches in excel I'm not sure I follow Kthom. I know you're probably pretty much an excel guru, based on your impressive threads with correct score predictions etc, but as I said, I'm not sure how that would work using those functions (mainly because I've never used them before). I googled 'em and large would return the k'th value, meaning I could pick the six labelled games prior to the one I'd like to analyse, but doing that I'd have to know those games labels, what I wanted was to have it done automatically. I'm truly grateful for your advices all of you who replied, I'm just not figuring out how to incorporate it into my sheet :/

[KThom]

Re: Need programming help for last 6 matches in excel If I could post attachments i could quickly show you. Try labelling column A 1-380, and coulmn BU the same. (Using football-data sheets). In Sheet 2 put a team in cell A2, and in B2 paste

and this should return the referee from the selected teams last game. This can be modified to suit. I have assumed the league you have chosen is on "Sheet 1"

=IF(ROWS(B$1:B2)
as an array forumla (Ctrl Shift Enter).

You can then drag this as far down as you like.

In C2 put 
[CODE]=VLOOKUP(B2,Sheet1!A:L,12,FALSE)

[Pepen]

Re: Need programming help for last 6 matches in excel Thanks alot Kthom, this way I get the specific numbers of the matches in which the specified team on sheet 2 has participated, right? And this only home or away, so ofcourse I'll just make 2 arrays, one for each. With this info, I suppose it should be possible to select the last 6th by using the large function and returning the last 6 largest values, of matches and from there, take whatever info I need. I'll toy around alittle and see what I can manage, but I'll probably come back asking for additional help some time.

[KThom]

Re: Need programming help for last 6 matches in excel By dragging the formula given down - each cell should automaically give the 2nd,3rd,4th etc largest value. You can then lookup the match number to return whatever value you need

[Pepen]

Re: Need programming help for last 6 matches in excel But won't that just return the last matches at home? since it looks up Sheet1!$C3:$C382, when I try to alter this to Sheet1!$C3:$D382 it gives an error. (so I guess the function can't search 2 coloumns?).I have altered it so it searches away matches Sheet1!$D3:$D382, and eventually I've managed to sort the home and aways, so it returns e.g. the accumulated points for the last 6 home AND aways, but when I then try to get this info into sheet3, I find I have problems since i do a VLOOKUP for the specific match, in my sorted data, which ofcourse will return 2 values. I'm not sure how I can select if it's home or away I want inserted into the specified cell, without having to do it manually, for every game. Meaning that I'd have to choose the cell, go into sheet2 and specify the arrea for searching to team1's homegames and then ind the next cell in the row, do this for team2's awaygames. That would take alittle time with 380matches + additional seasons :/ Hope you understand what I mean :)

[KThom]

Re: Need programming help for last 6 matches in excel The function is built to look up only the homes, but if you have now added tge aways, why not just use the "if" function to decide which column to lookup using the greater than / less than criteria?