Spreadsheet Help?

[epic73]

.

 

[Luke Imp]

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

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.

 

[Son of Cod]

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.

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?

 

[Dave]

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:

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.

 

[epic73]

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.

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.

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?

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

I think you'd be better off with numerous tables such as one per team with all their fixtures

I think I'll do this, thanks!