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>

 

[bluegold]

It works! Thank you so much Marcelo.

 

[Marcelo Branco]

It works! Thank you so much Marcelo.

You are very welcome and thanks for the feedback.

M.