I am trying to get my soccer stats spreadsheet to do some complicated adding up for me and not getting anywhere fast. I have the following spreadsheet with the season's game results so far, set up so I can copy and paste them straight from England - Premier League to update the totals on the right and bottom. A separate tab has the current league table, which updates the number of games played on the left, and the league position on the left and across the top.
Excel 2012
<tbody>
</tbody>
Any ideas of where to start greatly appreciated.
Excel 2012
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Games played | Lge Pos. | 4 | 13 | 9 | 2 | 16 | 7 | 18 | 14 | 1 | 3 | 6 | 15 | 10 | 12 | 20 | 19 | 5 | 8 | 11 | 17 | Goals scored at home | Goals conceded at home | |
2 | Arsenal | Bournemouth | Burnley | Chelsea | Crystal Palace | Everton | Hull City | Leicester City | Liverpool | Manchester City | Manchester Utd | Middlesbrough | Southampton | Stoke City | Sunderland | Swansea City | Tottenham | Watford | West Bromwich | West Ham Utd | |||||
3 | 11 | 4 | Arsenal | 3-0 | 3-4 | 0-0 | 2-1 | 3-2 | 1-1 | 12 | 8 | ||||||||||||||
4 | 11 | 13 | Bournemouth | 1-0 | 6-1 | 1-3 | 1-2 | 0-0 | 1-0 | 10 | 6 | ||||||||||||||
5 | 11 | 9 | Burnley | 0-1 | 3-2 | 2-1 | 1-1 | 2-0 | 0-1 | 2-0 | 10 | 6 | |||||||||||||
6 | 11 | 2 | Chelsea | 3-0 | 5-0 | 3-0 | 1-2 | 4-0 | 2-1 | 18 | 3 | ||||||||||||||
7 | 11 | 16 | Crystal Palace | 1-1 | 2-4 | 4-1 | 0-1 | 0-1 | 7 | 8 | |||||||||||||||
8 | 11 | 7 | Everton | 1-1 | 3-1 | 1-0 | 1-1 | 2-0 | 8 | 3 | |||||||||||||||
9 | 11 | 18 | Hull City | 1-4 | 0-2 | 2-1 | 0-1 | 2-1 | 0-2 | 5 | 11 | ||||||||||||||
10 | 11 | 14 | Leicester City | 0-0 | 3-0 | 3-1 | 0-0 | 2-1 | 1-2 | 9 | 4 | ||||||||||||||
11 | 11 | 1 | Liverpool | 5-1 | 4-1 | 0-0 | 6-1 | 2-1 | 17 | 4 | |||||||||||||||
12 | 11 | 3 | Manchester City | 4-0 | 1-1 | 1-1 | 1-1 | 2-1 | 3-1 | 12 | 5 | ||||||||||||||
13 | 11 | 6 | Manchester Utd | 0-0 | 4-1 | 1-2 | 2-0 | 1-1 | 8 | 4 | |||||||||||||||
14 | 11 | 15 | Middlesbrough | 2-0 | 1-2 | 1-1 | 1-2 | 0-1 | 5 | 6 | |||||||||||||||
15 | 11 | 10 | Southampton | 3-1 | 0-2 | 1-1 | 1-0 | 1-1 | 6 | 5 | |||||||||||||||
16 | 11 | 12 | Stoke City | 1-4 | 2-0 | 3-1 | 0-4 | 1-1 | 7 | 10 | |||||||||||||||
17 | 11 | 20 | Sunderland | 1-4 | 2-3 | 0-3 | 1-2 | 1-1 | 5 | 13 | |||||||||||||||
18 | 11 | 19 | Swansea City | 2-2 | 0-2 | 1-2 | 1-3 | 1-3 | 0-0 | 5 | 12 | ||||||||||||||
19 | 11 | 5 | Tottenham | 1-0 | 1-1 | 1-1 | 2-0 | 1-0 | 6 | 2 | |||||||||||||||
20 | 11 | 8 | Watford | 1-3 | 2-2 | 1-2 | 1-0 | 3-1 | 8 | 8 | |||||||||||||||
21 | 11 | 11 | West Bromwich | 1-2 | 0-4 | 0-0 | 1-1 | 4-2 | 6 | 9 | |||||||||||||||
22 | 11 | 17 | West Ham Utd | 1-0 | 1-1 | 0-3 | 1-1 | 1-0 | 2-4 | 6 | 9 | ||||||||||||||
23 | Goals Scored Away | ||||||||||||||||||||||||
24 | 12 | 3 | 1 | 8 | 9 | 7 | 5 | 4 | 13 | 13 | 8 | 5 | 6 | 6 | 4 | 5 | 9 | 7 | 6 | 5 | |||||
25 | Goals Conceded Away | ||||||||||||||||||||||||
26 | 3 | 10 | 9 | 6 | 11 | 10 | 13 | 14 | 10 | 5 | 9 | 6 | 7 | 8 | 8 | 9 | 4 | 11 | 6 | 11 |
<tbody>
</tbody>
Sheet1
I have used formulas to sum the first or last digit in a group of cells to get the total goals scored/conceded at home and away, for example Arsenal's total goals scored at home is calculated by {=SUM(IF(ISNUMBER(LEFT(D3:W3,1)+0),LEFT(D3:W3,1)+0))}. The next thing I want to work out is goals scored/conceded using the teams' league positions as criteria, but I can't think how to narrow down the criteria in this way. Here's what I have tried to get it to work out with no success: - Total number of games played against a team currently in a higher league position (home and away)
- Total number of games played against a team currently in a lower league position (home and away)
- Total number of goals scored against a team currently in a higher league position (home and away)
- Total number of goals scored against a team currently in a lower league position (home and away)
- Total number of goals conceded against a team currently in a higher league position (home and away)
- Total number of goals conceded against a team currently in a lower league position (home and away
Any ideas of where to start greatly appreciated.