General Betting
There is currently 1 person viewing this thread.
If i have a list of football fixtures (the full season to date) in - oldest to newest date order descending down a column
can I find a way of say finding the last six games featuring say Melchester Rovers, and calculate their record for a current form league table?
can I find a way of say finding the last six games featuring say Melchester Rovers, and calculate their record for a current form league table?
sort by:
Show
per page
Replies: 12
|
By:
No need - they win all their matches with a last minute scorcher.
|
|
By:
Yes it can. too difficult to put into this forum.
|
|
By:
http://en.wikipedia.org/wiki/Visual_Basic_for_Applications
have fun |
|
By:
I'll put this on hold, I've a book coming on VBA.
Will have a go once i've had a shufty through that. |
|
By:
Without using VBA you could try the following:
Assuming you already have columns for Home team and Away team, add columns to your spreadsheet for Home points, Away points, Home goals and Away goals. Now cut and paste your Away team details directly below the corresponding Home team details. You should now filter to add a games played column for each team. Throw all this into a Pivot Table and filter for whatever table you need. Actually, you'd be better to just wait for that book! |
|
By:
You can create a new column with the Game # for each team. You can use a COUNTIF formula to work out the number of times the team appears in higher rows, then +1 to work out the game #.
Then you can create another column giving a unique ID for each game, =teamname&Gamenumber. So for every match you will now have Arsenal6, Manchester United17 or whatever. Then wherever you want to put the 6-match form you can use Vlookups. =VLOOKUP(TeamName&(GameNumber-1,Range,Offset). Then replace -1 with -2, and so on up to -6. Hope that makes sense, hard to explain on a forum. |
|
By:
It is probably easier in a database. (e.g. Access)
You'd append the last 6 results for each team to a new table with a column for points. You'd then do a total of points for each team and then a query to sort the last 6 week league by descending points. I think I could do this fairly easily. |
|
By:
Cheers Knight Rider I'll have a play with that.
|
|
By:
Finally got round to this.
That worked fine Knight Rider, I owe you a beer. |
|
By:
I adopted a hybrid of FK's suggestion, I've just run into one small problem that's stopping me getting this fully solved.
I have vlookup to look for all fixtures in the 12th round of games If say Chelsea have only played 11, then it will return for row Chelsea12 N/A for the rest of the row. I then run a test which I thought would get round this a cell where the formula is =if(m4670,a476,0), I was hoping that this would convert the N/A into a 0, which i could then work around, unfortunately it doesn't. Any suggestions on how to get the N/A treat as a zero? |
|
By:
FK should read KR.
|
|
By:
Found out how to do it.
I'm getting ok at this Excel malarkey and will only ask for help on here when i've exhausted all other avenues. Thanks to all the people who've helped me with stuff over the years and have got me to this point. |