1. We are the friendliest, busiest football forum on the net.
    Sign up and join the fun, hundreds of members are online and posting right now, and logged in users see 50% less adverts!
    This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies.
    Learn More.

Spreadsheet Help?

Discussion in 'Tech Discussion (Help, Support, Tutorials)' started by epic73, Aug 18, 2017.

  1. epic73

    epic73 Well-Known Member

    Joined:
    Aug 13, 2015
    Messages:
    4,038
    Likes Received:
    592
    Location:
    Sunny California
    Supports:
    Manchester United... And Ipswich, apparently
    I'm making a spreadsheet ranking football teams, and each team has a "multiplier" based on how good they are, and teams get points based on their opponent's multipliers and whether they win or draw. On the 2nd sheet, the teams on the left are home and teams on the right are away, but I've just realized that the number of points in a match doesn't indicate to the sheet whether the home or away team won. Could someone help with this? Sorry if I'm making it confusing, I just need a way to format it so that it shows who won which match.

    Sheet: https://docs.google.com/spreadsheets/d/1devtTITpSmyghUN0LQnwLnPAzpXwFDoSBXm-_tcUazE/edit?usp=sharing
     
  2. Luke Imp

    Luke Imp Well-Known Member Staff Member Moderator

    Joined:
    Jan 17, 2015
    Messages:
    5,136
    Likes Received:
    1,329
    Location:
    Lincoln
    Supports:
    Lincoln City
    You'll have to manually input the result i.e. home or away win? You can't imagine you'll be able to set up an excel to draw results from somewhere and automatically do it.

    If that's what you're asking. I was with you up until the bolded bit.
     
  3. Son of Cod

    Son of Cod Well-Known Member

    Joined:
    Jan 17, 2015
    Messages:
    2,136
    Likes Received:
    2,273
    Location:
    London
    Supports:
    Grimsby Town
    Excel is capable of doing that, I think it's a feature called a web query but it's pretty high end intermediate/lower advanced stuff. Not sure if Google Docs can do it.

    With regards to your sheet Epic, can you explain it a bit more. I don't really get what I'm looking at. What do you mean by "multiplier"? Handicap? Where have the values come from?
     
  4. Dave

    Dave Well-Known Member

    Joined:
    Jan 18, 2015
    Messages:
    411
    Likes Received:
    188
    Location:
    .
    Supports:
    Tottenham
    Looking at sheet 2 and ASSE v Nice.

    Are you trying to say ASSE beat Nice so they get 3 points for the win and multiply that by 4.263 as that is Nice's Overall Multiplier?

    In a manual way of doing it I think you possibly need another sheet with results. That sheet could be something basic like:

    upload_2017-8-18_10-1-25.png

    All you need to input is the Home Draw or Away column and then the result is an if statement that if = H then select column B then the multiplier is a vlookup of column B against your sheet 1 then the score is if C = H then 3 x E. I don't know what you're doing if it's a draw as both teams have different multipliers so which multiplier would you put in the box for home and away squares?

    If you wanted to indicate whether the home or away team won I would probably add a line under every line which was a vlookup to the other table which picked the HDA field and then maybe conditional format it so that home draw and away are different colours.

    Also, as it currently is, your tables are only picking up the home results for the totals so you might want to add the totals at the bottom as well to pick up away results if you need them. That table with the teams on the left and top is ok for a newspaper or something but I don't think it's great to use with a spreadsheet. I think you'd be better off with numerous tables such as one per team with all their fixtures and update them as they happen with a summary sheet at the top which looked up the data and entered how many home and away victories and the overall multiplier scores based on this and this could feed back into you front sheet with all the leagues.
     
    Son of Cod likes this.
  5. epic73

    epic73 Well-Known Member

    Joined:
    Aug 13, 2015
    Messages:
    4,038
    Likes Received:
    592
    Location:
    Sunny California
    Supports:
    Manchester United... And Ipswich, apparently
    I'm putting in a number where the score would be as points for the winner, but the SS can't tell who won just from the number.

    ^^^This is the part that matters Son of Cod Luke Imp

    I think I'll do this, thanks!
     
    Son of Cod likes this.

Share This Page

Users Viewing Thread (Users: 0, Guests: 0)