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