Quote:
Originally Posted by p45cal
So par v udi was postponed until 8th April 2015, week 29 (row 290)
If you extend your calculations in columns Z and to the right, to row 290 or more, then change the 29 in cell D290 to 24, it goes a long way to putting things right.
Before you make that change, you'll notice also, that 11 fixtures were played for week 29; that's going to snarl things up.
|
The reason why some weeks have more than 11 fixtures is because I bunched the posteponed fixtures) with the week that was just played. Their position and league status will change and so will their behaviour/tactical approach depending how far they are from certain positions so it's not possible to treat a fixture that's played on week 25 but it was meant as a week 5 fixture as such. I also look at the effects of weeks on results so not changing the week that was meant as an earlier that's played later would disturbe it for reasons mentioned above.
Your solution of changing weeks back to their originals don't seem to work. As you would see Udi is calculated to be on 8th position eventhough they're 13th on April the 8th as it would show in the expected result (N:O) and if you scroll above to week 24 you would see most teams' positions are off by one place for whatever reason.
Even if it did work it would be very time consuming. For a league like Italy where fixture postement is rare it might be practical but for leagues like England where fixture postponement is a regular occurence it would be very challenging.
My own solution wasn't too disimlar to this. I added the PPD fixtures in their original week regardless without inputing the result obviously and input their points from columns AA and AB (knew these might come handy somewhere down the line) into columns P and Q and it works perfectly. But again, it is very time consuming and error-prone because of its hands-on nature.
Quote:
Originally Posted by p45cal
This goes back to my msg#8 about what is a 'round' and how is it defined? At the moment, you've said it was league and week. Should it be something different?
However:
In your first message in this thread you said you wanted "to figure home and away teams' league position ahead of their match"
|
I can't think of anything else. I thought maybe assign everymatch series of numbers and calculate ranks considering last 20 entries in columns Z and AA but then again there will be instances where fixtures are PPD and this formula would take into account teams and their ranks more than once.
Quote:
Originally Posted by p45cal
I see that in your expected results you seem to have included the ranks of the missing udi and par calculated from an earlier week/round's values in columns AN:AO. However, the values for those teams are for fewer matches played, so I think they're not comparable… but you may say different.
|
Think I addressed this above.
I calculated positions you see in columns N and O with copy and paste method over the summer (which was very time consuming). Now I have realised I also need to know the point difference of teams with places below and above ahead of their match hence why I'm doing this again but hopefully with a faster/more effective method.
Quote:
Originally Posted by p45cal
I think it could be possible to look at the most recent values (regardless of week) for all the teams playing in a given league and use those scores in columns AN:AO to rank them. This would make the formula even more complicated, and with your version of Excel, longer, to the point that it might be easier to have a user defined function (macro function) which would need vba code and for macros to be enabled and the file would become a .xlsm file. Acceptable?
A Power Query solution might also be possible (built in to Excel since version 2016) and would look neater.
I'll await comments on all the above before doing anything.
|
I'm all for whatever that works most efficient and effectively regardless. As long as it quickens my work I'm willing to pick it up.
My original worksheet with over 60 seasons of data is a 'Macro-Enabled Workbook'. I don't know the technical term I coded in a VBA which speeds up my copy-pasting that I do week after week (happy to share it with you if you need).