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