Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Creating Football Stats Database in Excel

This is a discussion on Creating Football Stats Database in Excel within the Excel Questions forums, part of the Question Forums category; I run a fantasy football (Australian Rules) game and I'm trying to set up some things so I can easily ...

  1. #1
    New Member
    Join Date
    Sep 2011
    Location
    Melbourne
    Posts
    26

    Default Creating Football Stats Database in Excel

    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

  2. #2
    Board Regular
    Join Date
    Oct 2006
    Location
    日本
    Posts
    2,457

    Default Re: Creating Football Stats Database in Excel

    Assumptions:
    1) Your data is located in a sheet called "Database" with Column A being #, B being Player, C being HO, through column L being DT.
    2) You are creating a summary sheet called "Summary" with Column A being Player name, and columns B:K being HO through DT.

    In Column A of "Summary" make a list of all team names.

    In B2, copy this formula:
    =SUMIF(Database!$B:$B,Summary!$A2,Database!C:C)

    Now copy across to column K for each team you have in your league.

    If you also need to have a Games column, just add it (let's say column L) and put the following formula in cell L2:
    =COUNTIF(Database!$B:$B,Summary!$A2)
    Download an HTML Maker to show your data, and please wrap all code in [code][/code] tags so we can read it. My mind-reading add-in is on my other computer.

  3. #3
    New Member
    Join Date
    Sep 2011
    Location
    Melbourne
    Posts
    26

    Default Re: Creating Football Stats Database in Excel

    Quote Originally Posted by Sal Paradise View Post
    Assumptions:
    1) Your data is located in a sheet called "Database" with Column A being #, B being Player, C being HO, through column L being DT.
    2) You are creating a summary sheet called "Summary" with Column A being Player name, and columns B:K being HO through DT.

    In Column A of "Summary" make a list of all team names.

    In B2, copy this formula:
    =SUMIF(Database!$B:$B,Summary!$A2,Database!C:C)

    Now copy across to column K for each team you have in your league.

    If you also need to have a Games column, just add it (let's say column L) and put the following formula in cell L2:
    =COUNTIF(Database!$B:$B,Summary!$A2)
    Thanks for that.



    That worked for the over all team stats, but is there a way to do it for all of the players?

    With relation to the games column you suggested, would that be total games by each player or games played by each team?

  4. #4
    Board Regular
    Join Date
    Oct 2006
    Location
    日本
    Posts
    2,457

    Default Re: Creating Football Stats Database in Excel

    Without knowing your sheet format or what you are actually looking for, I'm at a loss.

    Could you download an HTML creator and post what you want/what your data looks like?
    Download an HTML Maker to show your data, and please wrap all code in [code][/code] tags so we can read it. My mind-reading add-in is on my other computer.

  5. #5
    New Member
    Join Date
    Sep 2011
    Location
    Melbourne
    Posts
    26

    Default Re: Creating Football Stats Database in Excel

    Am I able to upload the excel file?

    http://www.mediafire.com/?vs0h55h816b23eb

    What you will see is the tabs... Totals is how I would like the team lists with players added up from all of the Rounds tabs, so far there have been 4 rounds.
    The Rounds are set up in a way I could copy the data and save as an image to post. I copy that again in the database tab, the Summary is what you showed me and the Others tab has what else I would like to have displayed from the database.
    Last edited by wmvaux; Sep 8th, 2011 at 11:23 PM.

  6. #6
    Board Regular
    Join Date
    Oct 2006
    Location
    日本
    Posts
    2,457

    Default Re: Creating Football Stats Database in Excel

    I am unable to download your workbook. Sorry I can't be of more help.
    Download an HTML Maker to show your data, and please wrap all code in [code][/code] tags so we can read it. My mind-reading add-in is on my other computer.

  7. #7
    New Member
    Join Date
    Sep 2011
    Location
    Melbourne
    Posts
    26

    Default Re: Creating Football Stats Database in Excel

    I don't know how to use a HTML maker, so I'll show you some more pictures of what I want to be able to do.



    This is the one from the information you gave me (Total Team Stats)


    And here is one of the teams stats.... first one is how it should look with adding up Round 1 and Round 2....etc


    The only issue with adding the stats for each round is sometimes there are different players, so how do we automatically add a new player?

  8. #8
    New Member
    Join Date
    Sep 2011
    Location
    Melbourne
    Posts
    26

    Default Re: Creating Football Stats Database in Excel

    Quote Originally Posted by Sal Paradise View Post
    Assumptions:
    1) Your data is located in a sheet called "Database" with Column A being #, B being Player, C being HO, through column L being DT.
    2) You are creating a summary sheet called "Summary" with Column A being Player name, and columns B:K being HO through DT.

    In Column A of "Summary" make a list of all team names.

    In B2, copy this formula:
    =SUMIF(Database!$B:$B,Summary!$A2,Database!C:C)

    Now copy across to column K for each team you have in your league.
    Can this be used for the players instead of the teams?

  9. #9
    Board Regular
    Join Date
    Oct 2006
    Location
    日本
    Posts
    2,457

    Default Re: Creating Football Stats Database in Excel

    Yes.

    Just adjust the team name for the player name, and the column for team name with the column for player name. If you read the post you quoted -- it actually says for players (not teams) so you should be able to get it to work. If a new player comes along, just add his name to your player summary table and copy the formulas next to his/her name and it should work out.

    The problem with your screenshots is that it doesn't show the sheet names or cell names, so it's hard to write formulas that will work "as-is" for your data.

    To use an HTML Maker, just download one (like the one in my sig) and follow the instructions to give copies of your data via HTML (makes it easy to copy-paste and understand how your sheet is structured).
    Download an HTML Maker to show your data, and please wrap all code in [code][/code] tags so we can read it. My mind-reading add-in is on my other computer.

  10. #10
    New Member
    Join Date
    Sep 2011
    Location
    Melbourne
    Posts
    26

    Default Re: Creating Football Stats Database in Excel

    Ok, sorry for not showing sheet names or cell names

    Here is the Database sheet as you can see it has players from 3 teams



    Here is the Summary from before, but now I have renamed it Team Summary
    and you can also see I have a blank one called Player Summary which I will play with to get the players total summary of stats.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com