#1
|
|||
|
|||
Calculate league standings in a fixture list
I have been trying to make this work for days. No joy. Hopefully a kindly user in this forum can impart expert guidance and enlighten me on something I thought wouldn't be too difficult, but has turned out to be impossible(for me) Here's an outline of what I'm trying to achieve: I have a spreadsheet with match fixtures for the whole of season of 2014/2015 Each row represents one match in the league season. There are 2 teams in each row. One is the home team and the other the away team. Each row has cells for the total points won by the home and away team. The cells are in the same columns. There are 12 teams in the league. There are 228 matches in the season. I need to rank the teams in date groups from 1 to 12. Column B is fixture dates Column C is home teams Column D is away teams Column AQ is total points for home team Column AR is total points for away team Column AI is where home team ranks should go Column AJ is where away team ranks should go Not all teams play on the same date All team ranks should be between 1 and 12 to represent their league standing on that given date. I've tried all sorts of variations with the rank, countif formulas. But I can't get the worksheet to populate the league standings correctly. The last variation of my formula looked like this: =IF(COUNTIF(B:B,B2)=1,1,MOD(IF(RANK.EQ(AQ2,AQ2:AR1 98,0)>12,12,RANK.EQ(AQ2,AQ2:AR198,0)),12)+1) I've attached a screen shot of the worksheet. Hopefully you can see that it populated the columns with 0's. Any advice or insights will be much appreciated. |
#2
|
||||
|
||||
Hi and welcome
please post a sample sheet, pictures are useless to work with. Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Copy of the worksheet attached.
|
#4
|
||||
|
||||
The attached contains my first look at your problem. The file's a mess and is work in progress. I've used Power Query to try and get the results you want. Although Power Query may not ultimately be used (only used as a cross-check for formula results maybe), I'd like you to check the rankings in columns BK:BL; they're supposed to be how each team was ranked AFTER the game on that date. This will ensure I've understood you correctly and that the results are right
|
#5
|
|||
|
|||
Thank you so much. The level of effort users are prepared to make to help others is amazing.
You're certainly on the right track, but I believe I may not have been specific enough describing what I'm looking for. The league placings change after matches are played but there are 12 teams in the league. And teams are playing on different days. So that means fixtures can be played by teams placed lower in the league on a day when higher teams do not play. Meaning the rankings wont start at 1. Eg if the bottom six played each other on the same day and there were no other matches played then that's 3 fixtures and rankings would run from 6 to 12. I'll need to make excel aware of every teams league position before it calculates their new positions. It's surprisingly complex the more you get into it. I tackled it with VBA, and couldn't get it working properly. Now I'm going to have a go with python. If I can't accurately calculate the league positions with that I'll have to break the spreadsheet down and calculate league placings in their own worksheet. That'll be a huge pain in the backside. Thanks again for your time and input. I really appreciate it. |
#6
|
||||
|
||||
I thought that maybe I had taken account of every team's position (at the end of the match).
Take the first 3 rows of your source table where 6 teams play on the same day. At the end of that day I've got rankings and points as follows: 1. Inverness C 3.0620007 2. Motherwell 3.0000005 3. Kilmarnock 1.0010006 4. Dundee 1.000001 5. Hamilton 0.0000008 6. St Mirren -0.0299999 None of the other teams have played, so they don't come into the equation (perhaps those teams all count as 0, in which case St Mirren would be ranked 12th?). So taking those 6 teams as being all being 'higher' teams, the next day, 4 more teams (from the now 'bottom 6') play, with Dundee United ending up with 3.0930009 points, higher than all so far, so I'd expect them to be ranked 1st, no? You say, though, that these teams have to be ranked 7th or below ('if the bottom six played each other on the same day and there were no other matches played then that's 3 fixtures and rankings would run from 6[7?] to 12'), so Dundee United should be ranked 7? I don't get it. I may be getting this all wrong since I'm no fan of football (is this football!? maybe it's rugby?) and have no knowledge of it, even less of leagues and rankings - the only matches I've watched are world cup finals, and not all of them, oh, and I went to see Tottenham at home once to replace someone who couldn't go. This could be an advantage because I'm just like Excel from that point of view. Confirm for me some assumptions: 1. The points in columns HT Algorithm Points and AT Algorithm Points are cumulative over time. 2. That the most recent points value for a team is the points found in either the HT Algorithm Points column or the AT Algorithm Points column, whichever has the latest date. I'll take you through the process/logic/algorithm I've used when you've commented on the above. Meanwhile, could you look at my results and tell me the first time it goes wrong, and crucially, why, and what it should be? |
#7
|
|||
|
|||
Quote:
First though, regarding assumptions: 1. Yes they are. 2. Yes the dates show the total points scored by a team at that time. So your league standings go off at the very beginning. There are 12 teams in the league. And the league standings represent where all 12 are after a round of fixtures. The first games played on the 9th and 10th of August are effectively the first round of fixtures. Even although 2 teams haven't played. The next round of fixtures starts on the 13th. And the two teams who didn't play in the first round of fixtures, Celtic and Partick, play their first matches. While the other teams play their second matches. The first 5 fixtures will be ranked from 1 to 10, and next 6 from 1 to 12. Not easy to explain, and again apologies if I'm not doing it well. |
#8
|
||||
|
||||
So we're looking for rankings after each 'round'?
It's clearly not easy to define rounds using dates. How about ranking each team at the time they've played the same number of games? In the attached, a graphical way of showing what you might want to call rounds: In cell BE1 enter a game number, then conditional highlighting will highlight those teams having played that number of games, both in columns BE:BF and columns C:D. Would it be useful to have a team ranking comparing all teams having played the same number of games? Enter 36, or 37 or 38 in cell BE1 and you have neat clusters of 12 teams, but enter 35 and you'll still see 12 teams highlighted, but they're not contiguous. Is there, nevertheless, mileage in having rankings in corresponding cells? Clarified perhaps by also showing the count of games played so that people will know whether a team has a game 'in hand' or not next to its ranking? |
#9
|
|||
|
|||
Quote:
The purpose of the spreadsheet requires league standings by "round" which incorporates dates of matches and the number of matches played. There isn't an easy way to do it. Initially, I imagined ranking 6 fixtures and calling that a round would be fine. However, it's not really because a round doesn't always amount to 6 fixtures. The very first round has 10 fixtures, for example. The ultimate goal, no awful pun intended, is to track league positions for each team throughout the entire season. |
#10
|
|||
|
|||
Quote:
|
#11
|
|||
|
|||
Quote:
|
#12
|
|||
|
|||
Quote:
|
#13
|
||||
|
||||
Are there any websites which have such tables? I'm not looking for sites which explain how they calculate rankings (although that might be good), I'm looking for sites that simply show the type of thing you're looking for.
|
#14
|
|||
|
|||
Quote:
I found a few videos on youtube about creating tables for football leagues. They helped me in some ways, but didn't give me a way to calculate league positions from fixture results since I wasn't building a table.. Anyway, thanks again. Your time and effort have been very much appreciated. |
#15
|
||||
|
||||
Please mark thread as solved ( see thread tools) Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cross-reference with full context a numbered list inside another multilevel list (list style) | MatLcq | Word | 0 | 02-01-2021 06:00 AM |
Calculate a Date | shawn.low@cox.net | Mail Merge | 5 | 12-12-2019 03:22 PM |
are there any good free golf league programs out there | kener40 | Other Software | 0 | 03-28-2014 05:54 PM |
calculate age | userman | Excel | 8 | 06-02-2012 10:59 PM |
Calculate formula base of list menu | rkeles | Excel | 4 | 09-22-2010 12:38 AM |