Creating single dynamic footbal l eague tables for home, away and overall

[gko_87]

Hello Everyone. I am trying to create a dynamic League table for a football league. In my spreadsheet, I have the fixtures and results for the English premier league to the 7th round. There is also a standing table into which data is pulled.

The data in the standing table is then sorted dynamically in the Dynamic Table as more matches are played and scores entered. For every match a team wins, it gets 3 points, for every draw it gets 1 point and no point is awarded for losing a game.

Teams are ranked based on the following principles:

1. The team with the highest point takes the first position.
2. If there are two or more teams with exactly the same points, then the team with the highest goal difference takes the lead.
3. If two or more teams have exactly the same points and same goal difference, the team with the highest overall goals scored takes the lead.
4. If two or more teams have the same points, goal difference and same goals scores, then the team with the most away goals scored takes the lead.

(See ranking system included in the sheet to the right of the static table).
THE REQUEST: I know it is possible to have singles tables that dynamically gets sorted in a descending order as more matches are played and scores entered instead of two (static table and dynamic table) as is in my case, but I can’t figure out how to do this. But I know someone here at Excel Forums will give a lending hand. Please help out with this.

In sheet two, I have created three tables to be filled based on the above principles, one for home matches played, one for away matches played and the last for the overall matches played. I will appreciate all the help I can get with the above problem. It will be of great help and time saving if the formulas can ignore blank cells.

Thanks in advance.

PS: Please remember the above principles and also that a Win = 3 Points, a Draw= 1 Point and a Loss= 0; I will appreciate if the ranks and everything else in the three tables in sheet2 can get dynamically sorted in descending order (highest to lowest).

Football Simulatio.xlsx

[Pete_UK]

Bob Phillips has a league table file that you can download for free from his website here:

http://www.xldynamic.com/source/xld.LeagueTable.html

Hope this helps.

Pete

[gko_87]

Pete, thanks for your reply. I just downloaded the table. Its password protected and its not exactly what I expected. I already have data I have compiled in the format in the spreadsheet I attached. And it will take me time to start everything changing everything again.

It will also be a learning experience for me if I can be able to learn a new concept from the help I can get. From my table, you can see that I have collected some data. What I attached is just an extract of what I have compiled. I have data for more than 10 leagues already compiled. Is there this can be possible?

[gko_87]

Pete, the table you recommended I download isn't what I wanted though. I already have my own data compiled for about 14 football leagues and I wanted something I can adopt for my spreadsheet. Plus the table does not have other options like reviewing the ball possession, goal attempts, pass accuracies, clean sheets etc, which are other things I look at in my full spreadsheet.

It will also be a learning experience for me to be able to analyse formulas suggested here, see them in practical use and learn a new concept by trying them out out for other leagues. It will take a lot of time to start adopting the new format. I also wanted to build my own model which I can understand easily.

Any idea how it can be done? Will really appreciate if you can help out.

Thank you and Good day.

Onditi

[MarvinP]

Hi Onditi,

I'd recommend you change the structure of your table like the attached. Then you can easily add rows (two rows per game) and do Pivot Tables to get all the answers you need. See the attached.

Football Scorekeeper.xlsx

[gko_87]

Hello Marvin! Thank you for your response. But it isn't still what I desire. What i need is a situation where the teams are arranged in order the order in which they are in "Dynamic table" without having the standing table (see workbook attached). The team with the highest points should occupy the 1st positions (The principles above apply). What you have created is the 1st table (standing table). And the formats to be adopted are in sheet 2.

Thank you for taking the time to respond.

[gko_87]

To help understand the help I need, I am attaching the same workbook with a 3rd sheet added. In the 3rd sheet, I have filled the expected results for the Overall Table. Form the results you can see that:

1. The team with the highest points takes the first position.
2. Where two or more teams have the same points, the team with the highest goal difference should be above the other.
3. Where there are more than one team with the same goal points and goal difference, the team with the highest overall goals scored takes the lead.
3. And where there is a tie for 1,2 and 3 above, the team with the highest away goals should take the lead.

The above should apply to the Home and Away tables. What I need is to do away with the tables in sheet 1 (standing and dynamic tables) and just have the tables in sheet two.

Copy of Football Simulation.xlsx

[JohnTopley]

The results for your Overall Table (in "Expected results") are derived in the same way as your dynamic table: so have done the work, so I am not sure what more you expect.

In "Expected Results" (Overall) in B27

=INDEX(Sheet1!$AM$4:$AM$28,MATCH(ROWS($1:1),Sheet1!$AL$4:$AL$28,0))

Drag down

You can use VLOOKUP to get the other data from your "static" table I2:AG28

No need for your dynamic table.

Just create your HOME/AWAY RANKING tables and change the ranges in above formula.

[JohnTopley]

P.S

only formulae needing Ctrl+Shift+Enter as those with ...SMALL(IF( .......) (Array formula)

All others only require Enter

[JohnTopley]

I was adding the ranking tables for Home and AWAY and discovered in the OVERALL table that two teams are ranked 18.

I changed the RANK formula to the following:

=SUM($AN4,$AO4,$AP4,$AQ4)+ROWS($1:1)*10^-4

The addition then "removes" the duplicates by adding a small number to the ranking..

In B27 of the "OVERALL TABLE" in Expected Results I used this formula:

=INDEX(Sheet1!$AM$4:$AM$28,MATCH(SMALL(Sheet1!$AZ$4:$AZ$28,ROWS($1:1)),Sheet1!$AZ$4:$AZ$28,0))

This lists the ranks in order.

Note that in the RANK column A the duplicates of SWANSEA & SUNDERLAND re listed as 18 & 19 not 18=

[MarvinP]

Hi Onditi,

Once Again - would you consider keeping your data in the table format attached. You will need to enter each game twice, once for the Home Team and another line for the Away Team. This data structure makes calculating your desired result much easier.

In the attached table, you can change any of the goals and it will recalculate the Pivot Table automatically!!!
As you add games to the bottom of the list the resulting rankings will be added automatically..

The method for determining ties is done by first adding up the Points and then using the GoalDiff numbers and dividing them by 1000. This is added to the Total Points. Then the Total goals is added and then the goals away is added as fractional amounts. This formula keeps track of the tie breaker information.

I've added a Dynamic Named Range to your Data so the Pivot Table will work with all new rows added.
I've also added an Event Macro that will Refresh the Pivot whenever a score is added or changed.

Look at what I've put together and try to change some goal information and watch the Ranking Pivot change.
Add a few more games and see the Rankings change...

Football Scorekeeper2.xlsm

You asked for help from us "Experts" and this is my best answer. Change the data entry format and it will make answers much easier and faster.