Ranking formula

[bluegold]

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

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>

 

[Useful]

Hi, Vlookup() ?

 

[bluegold]

Any idea's or is it impossible?

 

[Jim885]

Just put a greater than ( > ) in your formula when comparing the two totals. Thus, the formula will favor one value when the two totals are equal, until one value is exceeded by the other.
Basically this, =If(competitionvalue>hometeamvalue,competitionvalue,hometeamvalue)

 

[bluegold]

Any working formula's? I'm no Excel Pro

 

[sanrv1f]

Few questions

1. Do we need to consider only those matches played in the past between the 2 teams in the row? or should we consider any match played by each team?
2. Should we account only the matches where the home team was the same as home team in current row, or should we consider all matches including the away matches?
3. F&A difference - is it for the current match or a cumulative total?

 

[bluegold]

Hi Sankar,

To answer your questions.
1. Any match played by each team.
2. All matches including away matches.
3. Its a cumulative total for past results (does not include current row results)

Hope this helps

 

[bluegold]

Sanrv1f,

This is an updated spreadsheet with an extra column that I forgot to include that greatly helps identify when one season ends and another begins
So to hopefully better explain the expected formula.

If we take cell A2. As there is no previous data to work on a "nd" is displayed.

If you take A10 for example now there is previous matches to work on to determine whether the home team is up or down relative to the away team on the ladder. Both teams have 0 points but the away team has the better For & Against total (-8 versus -14) so the home team = down

If you now take A26 you will see its the beginning of a new season so this is where it gets a little tricky. There is no previous data for this current season but there is previous data from last season. So the formula should look for the last known points tally & For & Against totals to determine whether the home team is up or down relelative to the away team.

Once there is sufficient results tallied for the current season than the formula only works on the current season and doesn't look at previous season.

Am I asking too much ? lol

An easier way I suppose is to treat each season seperately and display a "nd" at the beginning of each season when no previous results are available to determine table position?

	A	B	E	AQ	AR	AS	AT	AU
1	Home	Away	Table Position	Home Team Points	Home F&A Difference	Away Team Points	Away F&A Difference	Season
2	souths	sydney	nd	nd	nd	nd	nd	1
3	gcoast	nqld	nd	nd	nd	nd	nd	1
4	newcastle	canberra	nd	nd	nd	nd	nd	1
5	parramatta	canterbury	nd	nd	nd	nd	nd	1
6	manly	cronulla	nd	nd	nd	nd	nd	1
7	wests	sgeorge	nd	nd	nd	nd	nd	1
8	brisbane	penrith	nd	nd	nd	nd	nd	1
9	melbourne	nzealand	nd	nd	nd	nd	nd	1
10	souths	canterbury	down	0	-14	0	-8	1
11	sydney	brisbane	down	2	14	2	36	1
12	newcastle	manly	up	2	16	0	-6	1
13	penrith	canberra	down	0	-36	0	-16	1
14	nqld	wests	down	0	-18	2	8	1
15	nzealand	parramatta	down	0	-14	2	8	1
16	melbourne	cronulla	up	2	14	2	6	1
17	sgeorge	gcoast	down	0	-8	2	18	1
18	brisbane	nqld	up	4	42	0	-38	1
19	parramatta	newcastle	down	2	-6	4	17	1
20	sydney	melbourne	down	2	8	2	13	1
21	canberra	sgeorge	down	2	-12	2	10	1
22	gcoast	cronulla	down	2	0	4	7	1
23	canterbury	wests	down	2	5	4	28	1
24	penrith	souths	down	0	-40	0	-27	1
25	manly	nzealand	down	0	-7	2	0	1
26	canterbury	sydney	up	nd	nd	nd	nd	2
27	melbourne	brisbane	down	nd	nd	nd	nd	2
28	nqld	parramatta	down	nd	nd	nd	nd	2
29	sgeorge	cronulla	down	nd	nd	nd	nd	2
30	gcoast	canberra	up	nd	nd	nd	nd	2
31	nzealand	newcastle	down	nd	nd	nd	nd	2
32	manly	souths	up	nd	nd	nd	nd	2
33	wests	penrith	up	nd	nd	nd	nd	2

<tbody>
</tbody>

 

[bluegold]

Sankar,

You haven't forgotten about me have you? Is there anything you don't understand?

Regards,
Pete

 

[bluegold]

hmm one month and still no answer, a bit disappointing