Still getting NAME error.

Attached Images

_xlfn.png (98.5 KB, 8 views)

I've dealt with the VSTACK one, the rest will have to wait until tomorrow (UK).

No problem mate, it's past 1:00 there. You get some rest.

try in AI2:Revert to FILTERXML formula for columns AB:AC (TEXTSPLIT is a problem at your end). No don't, see edit below.

Bed.


edit. use in cell AB2 and copy down and across, it's safer (includes VALUE function).

Weldone mate works like a charm.

Without these formulas I may had to do these the old fashion way which was to copy paste results week by week (something I did during the summer). This would save me hours/days/weeks.
Are you available on the weekend/next week because I'm planning to mass calculate my data then? Your formulas look sound and solid and hopefully I won't be needing you again but I'll let you know if I encounter any issue.

I'm really gratefule for your help again.

Hi Pascal,

The formula works great when teams have played equal number of games every matchday/week. However, when there are less than 10 matches played for whatever reason the positions are calculated wrongly as you can seen in the spreadsheet below for week 24. 'par' v 'udi' is postponed in that particular week and thus teams are ranked from 1 to 18. The expected results are in columns N and O. What do you think is a solution for that little problem?

Attached Files

serie_A_2014-15_(MSF).xlsx (142.3 KB, 1 views)

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.

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 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.

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.

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.

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.

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.

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).

Then maybe instead of basing your calculations on date of games, base them on scheduled weeks instead, e.g. as number like 202401. When the game was postponed, it will not accounted until it is really played. And then it will be accounted for scheduled week, not for week it was really played at.

I'm not basing it on dates but weeks rather. The reason why the formula fails is because it ranks teams from 1-18 rather than 1-20 when there is A PPD fixture (excluding teams that didn't play from the rankings basically). if two matches are PPD then teams will be ranked 1 to 16.

'base them on scheduled weeks instead, e.g. as number like 202401. When the game was postponed, it will not accounted until it is really played. And then it will be accounted for scheduled week, not for week it was really played at.'

Would you elaborate more, please?

I've managed, using Power Query (green columns below), to get the rankings for week 24 to tally with your expected rankings in columns N:O :
2024-01-08_022418.jpg
I've done this by including the missing teams (par & udi) rank values from their most recent rank values before week 24 (from week 23 in this case):
2024-01-08_023145.jpg


However, with week 29, when the postponed par/udi fixture was played, there are 2 fixtures each played by udi & par:
2024-01-08_023531.jpg
Here, par and udi have both taken part in 2 fixtures each in week 29, you can see that cells BJ283:BJ284 are blank because it's taken account only the most recent (last) fixtures' rank values for those 2 teams when calculating ranking. I see you've got the same ranks (20 and 13) for week 29 when there are in fact different rank values which would alter their ranks.
How did you go about assigning those ranks/positions?

If you do that then you would be excluding their result obtained in week 23 which then would lead to wrong rank value and thus possibly wrong ranking if there are teams close to them.

Yes, as I explained some weeks have more than 10 matches. I purposely did that. The reasons for that are explained in my previos comment.
I obtained those ranking with my built table. It's flawless but very time consuming for massive data and almost impractical. I'd be happy to share a basic version on here if you would like.

Also, please note that 'par' were given points penalty that season and were deducted points at different times so if you happen to calculate their points and see different points tally to mine then that would be the reason. However, Udi's point tally should come out same as mine.

No I'm not, they're used again.

I realise that, the question is, how to handle it?

Yes please.

I'm getting an error when trying to attach it. Something about security token.

'Your submission could not be processed because a security token was missing.

If this occurred unexpectedly, please inform the administrator and describe the action you performed before you received this error.'

Would you be able to PM me your email so I would send it to you there; or guide me to deal with this error?

^^^Ignore this, the file was too big^^^


Yes, Is it possible to use dates? It would produce a much more valid and accurate data; but I imagine the current formula will still leave out teams if dates are used. It will rank teams 1-4 when 4 teams play on a certain date for example.
I also plan to calculate positions in international football. In International football a lot of times groups are made up of odd number of teams meaning one team will sit out every matchday. This formula would fail to recognise that there also.


Find the spreadsheet below. I basically copy-paste results of weeks from 'Data' onto A30 : D39 on 'Standings'. The results will then be copy-pasted to 'MinedData'

The table on 'Standings' would take date of 1st match of the week and calculate point totals, goal diffs and all of that for every team before that date and adjust rankings.

When there are PPD matches I do them separately. They usually stick out since they're played in midweek (Tue, Wed, Thu).

It usually takes 20-30 mins to calculate a regular season's whole data and a whole lot more for other competitions such as World Cup/Champions League etc....

Attached Files

Super_Data_Extractor_MSF.xlsx (383.4 KB, 2 views)