Creating Football Stats Database in Excel

[wmvaux]

I run a fantasy football (Australian Rules) game and I'm trying to set up some things so I can easily get the information I want displayed via an excel worksheet, pretty much a database that can add up players stats from all games played and to have things like top 10 goal scorers, top 10 kicks....etc

There are 6 teams and after game has been simulated, the program saves a text file that I have been copying and pasting into excel, this is how it looks for one team

I would like have the end product look like this also, but with totals of all games for each player, one of the issues is not all the same players play each week, so I can't simply just add the same cells type of thing.

There have been 4 rounds so far and I have tried putting all the teams stats one after another for each round and trying to use a pivot table, but I just can't get it to look the way I want it to look.

I know half the battle is setting this up the right way, I think my problem is I try to set it up from how I want it to look.

I realise there are going to be many steps involved with this, but I know the end product will be worth it I'd appreciate some help or advice if people can understand what I'm after

I am using Excel 2010

 

[Sal Paradise]

Assuming your player name is in A2 on a sheet called Players Summary

=SUMIF(Database!$C:$C,'Players Summary'!$A2,Database!D:D)

 

[wmvaux]

Thank you, I've been able to get that to work as you can see here

I have added the team summary details below the Totals as a cross reference to make sure they both add up.

Is there a way to create this from the players summary, if I created another sheet called Top Summary?

So a table that picks out the top 10 values for different columns

 

[Sal Paradise]

Yes. Look at help for the LARGE() function or the SMALL() function if lower is better for a category. It depends on how you want the people ranked (as a composite of their rank in each category, or on total points somehow, or whatever).

For instance, for ranking your goal kickers, you can use the following:
=LARGE(K:K,1)

That will give you the largest value for the "G" column.

Let's say you put that in cell C2.

To get the name of the team, you would use:
=INDEX(C:C,MATCH(C2,K:K,0),0)

Adjust the C:C for whatever column you want data from (so L:L for the "B" category for instance).

This will run into problems if two people have the same score.

You could create "Helper Columns" using the RANK() function to get the appropriate ranks (with duplicates) and then lookup those values.

To get the rank of each player (using column K as an example):
=RANK(K2,K:K)+COUNTIF(K$2:K2,K2)-1

It gets more complicated from there. But it can be done.

 

[wmvaux]

=LARGE(K:K,1) works for the most goals in a game, but I'm after the player with the most goals aggregate.

 

[wmvaux]

I was able to work out the top 10 as you can see in the picture =LARGE(K:K,1) then =LARGE(K:K,2) then =LARGE(K:K,3)...etc, but how do I display the player and team too? The team is in column A in white text so you can't see. Also I have a top 10 for leading disposals which is K+H (column E + column G) I haven't been able to get that to work.

 

[Sal Paradise]

There are two ways to go. You can use "helper columns" which you can hide that have calculations to make sure you can look everything up right, or you can use array formulas which take a while to calculate with lots of data depending on how many you have.

It may be better off to work with Pivot Tables which can do this sort of thing much more easily.

Basically, the theory in getting the team name/player name is to get the top value, and then lookup that value in your table. If you have duplicates this will cause problems.

For instance, for your Goal Kickers table Team value (cell Q5):
=INDEX(A:A,MATCH($S5,$K:$K,0),)
Copy across and down for the goal kickers table.

That will give you the team name in column A corresponding to the value 23 in column K. If you have two players with 23 in column K, you won't get the right answer (you'd get the same value in consecutive rows since MATCH can only find the first value).

To get around that, you'd make a helper column (column N) which reads:
=RANK(K2,K:K)+COUNTIF(K$2:K2,K2)-1

That will give you the rank (1st, 2nd, 3rd, etc.) of each value in column K. If there are two 23s, the first will be ranked 1, and the second will be ranked 2 because of the above formula.

Then you can get your table by looking up the rank.

In column Q:
=INDEX(A:A,MATCH(ROWS($1:1),L:L,0),)

In column R:
=INDEX(B:B,MATCH(ROWS($1:1),L:L,0),)

In column S:
=INDEX(K:K,MATCH(ROWS($1:1),L:L,0),)

Do similar helper columns for the other stats to make it work out okay. For combined columns, you would create two helper columns -- one to add the stats together, and one to rank them.

You may want to create another sheet called "RANK" just to create ranks for all the columns and do the math -- that way you won't clutter up your main sheet.