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.

Attached Images

2023-09-22_16-27-03.jpg (201.1 KB, 43 views)

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

Copy of the worksheet attached.

Attached Files

League Standings Sample Sheet.xlsm (69.6 KB, 7 views)

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

Attached Files

msofficeforums51391League Standings Sample Sheet.xlsm (107.9 KB, 2 views)

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.

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?

Yes, sorry. It's my fault for not being more specific in the OP.
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.

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?

Attached Files

msofficeforums51391League Standings Sample Sheet02.xlsm (113.8 KB, 1 views)

It's a deceptively tricky task.
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.

Actually, there is a way to confirm the start of a fresh round of fixtures. I added the no of games played by the home team and away team in columns E & G. When the number increments in any column, a fresh round of fixtures begins. It doesn't matter if the match numbers are not the same. The highest number of games played confirms which round of fixtures it belongs in.

Attached Files

SPFL 2014 - 2015 Revision.xlsx (57.9 KB, 1 views)

In this revision i've highlighted the first instance of each round in the games played column for both teams. The first time a number is highlighted in either column signals the start of a new round. The number of fixtures per round can be quite variable. For example round 4 has 7 fixtures, and round 5 has 18 fixtures!

Attached Files

SPFL 2014 - 2015 Revision.xlsx (58.0 KB, 2 views)

Yep, I screwed this up. When I've made a correction I'll repost the spreadsheet.

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.

I managed to do it, and I want to say a big thanks to you. It was the exchanges we had around how to calculate league positions that got me to a solution. A key stage I'd left out involved ranking after adding home and away games together. What I've got now isn't too pretty. The ranking will not work unless the formula is dragged down round by round, but it does work. So I'll revisit the solution when I've got some time and see if I can code something automated with python. That's gonna take a while because my coding aint too sophisticated

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.

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