Ok so I've got a spreadsheet of league matches and I need a formula to determine whether the Home Team (ColA) is up or down the ladder in comparison to their Away team opponent (ColB) according to the data in columns AQ, AR, AS & AT.
ColAQ - Home team competition points
ColAR - Home team points scored - points conceded = points difference (To be used as a tiebreaker when both Home & Away teams are on equal competition points)
ColAS & AT are the same as above but relating to the Away team.
ColE - The table formula which I have filled out with the desired results.
Now you will see in columns AQ - AT that the spreadsheet begins with cell entries "nd" for the first 9 rows. This is because their is no previous data available The next lot of "nd" cell entries beginning on row 26 is when a new season has begun. The table formula must look for the last available data before it returns a "nd", in this instance it had previous data to still determine table position relative to the away team.
If two team's are on equal competitions points and equal points difference then the home team should be up by default.
If there are any questions don't hesitate to ask![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
Notes:
Using Excel 2007
nd = Acronym for "No Data" (Custom cell format)
<tbody>
</tbody>
ColAQ - Home team competition points
ColAR - Home team points scored - points conceded = points difference (To be used as a tiebreaker when both Home & Away teams are on equal competition points)
ColAS & AT are the same as above but relating to the Away team.
ColE - The table formula which I have filled out with the desired results.
Now you will see in columns AQ - AT that the spreadsheet begins with cell entries "nd" for the first 9 rows. This is because their is no previous data available The next lot of "nd" cell entries beginning on row 26 is when a new season has begun. The table formula must look for the last available data before it returns a "nd", in this instance it had previous data to still determine table position relative to the away team.
If two team's are on equal competitions points and equal points difference then the home team should be up by default.
If there are any questions don't hesitate to ask
Notes:
Using Excel 2007
nd = Acronym for "No Data" (Custom cell format)
A | B | E | AQ | AR | AS | AT | |
1 | Home | Away | Table Position | Home Team Points | Home F&A Difference | Away Team Points | Away F&A Difference |
2 | souths | sydney | nd | nd | nd | nd | nd |
3 | gcoast | nqld | nd | nd | nd | nd | nd |
4 | newcastle | canberra | nd | nd | nd | nd | nd |
5 | parramatta | canterbury | nd | nd | nd | nd | nd |
6 | manly | cronulla | nd | nd | nd | nd | nd |
7 | wests | sgeorge | nd | nd | nd | nd | nd |
8 | brisbane | penrith | nd | nd | nd | nd | nd |
9 | melbourne | nzealand | nd | nd | nd | nd | nd |
10 | souths | canterbury | down | 0 | -14 | 0 | -8 |
11 | sydney | brisbane | down | 2 | 14 | 2 | 36 |
12 | newcastle | manly | up | 2 | 16 | 0 | -6 |
13 | penrith | canberra | down | 0 | -36 | 0 | -16 |
14 | nqld | wests | down | 0 | -18 | 2 | 8 |
15 | nzealand | parramatta | down | 0 | -14 | 2 | 8 |
16 | melbourne | cronulla | up | 2 | 14 | 2 | 6 |
17 | sgeorge | gcoast | down | 0 | -8 | 2 | 18 |
18 | brisbane | nqld | up | 4 | 42 | 0 | -38 |
19 | parramatta | newcastle | down | 2 | -6 | 4 | 17 |
20 | sydney | melbourne | down | 2 | 8 | 2 | 13 |
21 | canberra | sgeorge | down | 2 | -12 | 2 | 10 |
22 | gcoast | cronulla | down | 2 | 0 | 4 | 7 |
23 | canterbury | wests | down | 2 | 5 | 4 | 28 |
24 | penrith | souths | down | 0 | -40 | 0 | -27 |
25 | manly | nzealand | down | 0 | -7 | 2 | 0 |
26 | canterbury | sydney | up | nd | nd | nd | nd |
27 | melbourne | brisbane | down | nd | nd | nd | nd |
28 | nqld | parramatta | down | nd | nd | nd | nd |
29 | sgeorge | cronulla | down | nd | nd | nd | nd |
30 | gcoast | canberra | up | nd | nd | nd | nd |
31 | nzealand | newcastle | down | nd | nd | nd | nd |
32 | manly | souths | up | nd | nd | nd | nd |
33 | wests | penrith | up | nd | nd | nd | nd |
<tbody>
</tbody>