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>

 

[Scott Huish]

It's not clear to me what you are after exactly, and I'm guessing no one else either. Let's take one step a time. Column AQ for example.
Why is the value in AQ10 0? How would you figure it out manually?

 

[bluegold]

I have hidden columns that contain other formula's to try and simplify and avoid information overload. Column AQ10 is 0 because the team "Souths" have 0 points accumulated as they lost their prior match. Would you like me to reveal more columns ?

 

[bluegold]

Ok I agree that my question is convoluted to say the least. Ignore any of my previous thread entries and I'll attempt to better explain myself below.

This is what I want the formula in ColE to do.

Determine whether ColA (Home Team) is higher ("up") or lower ("down") on the league ladder compared to ColB (Away Team).
To determine this the formula should reference ColAQ (Home team competition points) against ColAS (Away team competition points). If ColA has more points than ColB, the result in ColE will be "Up" and if colA has less points than ColB than the answer would be "down".
If both ColA & ColB are on equal competition points than the tiebreaker is used which is ColAR (Home Team For & Against Totals) & ColAT (Away Team For & Against Totals). If both teams are on equal competition points and equal For & Against totals, than ColE is to be "Up".

You will see that "No Data" is displayed when a new season begins (ColAU). This is because both Home & Away teams have not yet played a match and therefore have no competition points or For & Against totals to their name. This is irrelevant to the formula in ColE but its just to explain why the "No Data" is being displayed in the spreadsheet.

If there are any questions PLEASE do not hesitate to ask!

	A	B	E	AQ	AR	AS	AT	AU
1	Home Team	Away Team	League Ladder	Home Comp. Points	Home Team F&A	Away Comp. Points	Away Team F&A	Season
2	souths	sydney	No Data	No Data	No Data	No Data	No Data	1
3	gcoast	nqld	No Data	No Data	No Data	No Data	No Data	1
4	newcastle	canberra	No Data	No Data	No Data	No Data	No Data	1
5	parramatta	canterbury	No Data	No Data	No Data	No Data	No Data	1
6	manly	cronulla	No Data	No Data	No Data	No Data	No Data	1
7	wests	sgeorge	No Data	No Data	No Data	No Data	No Data	1
8	brisbane	penrith	No Data	No Data	No Data	No Data	No Data	1
9	melbourne	nzealand	No Data	No Data	No Data	No Data	No Data	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	No Data	No Data	No Data	No Data	2
27	melbourne	brisbane	down	No Data	No Data	No Data	No Data	2
28	nqld	parramatta	down	No Data	No Data	No Data	No Data	2
29	sgeorge	cronulla	down	No Data	No Data	No Data	No Data	2
30	gcoast	canberra	up	No Data	No Data	No Data	No Data	2
31	nzealand	newcastle	down	No Data	No Data	No Data	No Data	2
32	manly	souths	up	No Data	No Data	No Data	No Data	2
33	wests	penrith	up	No Data	No Data	No Data	No Data	2

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula AQ2 =IF(AI2="nd","No Data",SUM((AI2*2)+SUM(AJ2*1))) AR2 =IF(AI2="nd","No Data",SUM((AM2-AN2))) AS2 =IF(AK2="nd","No Data",SUM((AK2*2)+SUM(AL2*1))) AT2 =IF(AK2="nd","No Data",SUM((AO2-AP2))) <tbody> </tbody>

<tbody>
</tbody>

 

[bluegold]

Anyone? Don't jump in all at once now lol

 

[Marcelo Branco]

Hi,

Could you explain why E26 = up?

Last result for Canterbury: row 23 as Home
Points (AQ) = 2
Difference (AR) = 5

Last result for Sidney: row 20 as Home
Points (AQ) = 2
Difference (AR) = 8

2=2 a tie, but 5 < 8. So shouldn't the result in E26 be down? Or am i missing something?

M.

 

[bluegold]

Hi Marcelo,

Thanks for the reply, you have pointed out an error of mine.
E26 & > is the beginning of a new season as per ColAU, hence the results in E26 should equal "No Data" as there is no competition data to determine a table position yet.

	A	B	E	AQ	AR	AS	AT	AU
1	Home Team	Away Team	League Ladder	Home Comp. Points	Home Team F&A	Away Comp. Points	Away Team F&A	Season
2	souths	sydney	No Data	No Data	No Data	No Data	No Data	1
3	gcoast	nqld	No Data	No Data	No Data	No Data	No Data	1
4	newcastle	canberra	No Data	No Data	No Data	No Data	No Data	1
5	parramatta	canterbury	No Data	No Data	No Data	No Data	No Data	1
6	manly	cronulla	No Data	No Data	No Data	No Data	No Data	1
7	wests	sgeorge	No Data	No Data	No Data	No Data	No Data	1
8	brisbane	penrith	No Data	No Data	No Data	No Data	No Data	1
9	melbourne	nzealand	No Data	No Data	No Data	No Data	No Data	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	No Data	No Data	No Data	No Data	No Data	2
27	melbourne	brisbane	No Data	No Data	No Data	No Data	No Data	2
28	nqld	parramatta	No Data	No Data	No Data	No Data	No Data	2
29	sgeorge	cronulla	No Data	No Data	No Data	No Data	No Data	2
30	gcoast	canberra	No Data	No Data	No Data	No Data	No Data	2
31	nzealand	newcastle	No Data	No Data	No Data	No Data	No Data	2
32	manly	souths	No Data	No Data	No Data	No Data	No Data	2
33	wests	penrith	No Data	No Data	No Data	No Data	No Data	2

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula AQ2 =IF(AI2="nd","No Data",SUM((AI2*2)+SUM(AJ2*1))) AR2 =IF(AI2="nd","No Data",SUM((AM2-AN2))) AS2 =IF(AK2="nd","No Data",SUM((AK2*2)+SUM(AL2*1))) AT2 =IF(AK2="nd","No Data",SUM((AO2-AP2))) <tbody> </tbody>

<tbody>
</tbody>

 

[bluegold]

Any ideas people?

 

[Marcelo Branco]

Maybe this in E2 copied down

=IF(AQ2<>"No Data",IF(OR(AQ2>AS2,AR2>AT2),"up",IF(OR(AS2>AQ2,AT2>AR2),"down","up")),"No Data")

M.

 

[bluegold]

Thanks Marcelo for reply.

Problem I see with your formula is if a team has a better F&A record it automatically ranks higher regardless of the team's competition points which should be the main criteria. For example look at the following table which I have modified results to illustrate my point.

E23 your formula puts the Home team "Canterbury" as "Up" in the ladder in comparison to the Away Team "Wests" even though Canterbury only has 2 competitions points (AQ23) and Wests have 4? (ColAS23). The F&A should only be used as a tiebreaker when both teams have equal competition points.

	A	B	C	D	E	AQ	AR	AS	AT	AU
1	Home Team	Away Team	Win	Loss	League Ladder	Home Comp. Points	Home Team F&A	Away Comp. Points	Away Team F&A	Season
2	souths	sydney	$1.95	$1.85	No Data	No Data	No Data	No Data	No Data	1
3	gcoast	nqld	$1.62	$2.30	No Data	No Data	No Data	No Data	No Data	1
4	newcastle	canberra	$1.55	$2.45	No Data	No Data	No Data	No Data	No Data	1
5	parramatta	canterbury	$1.48	$2.65	No Data	No Data	No Data	No Data	No Data	1
6	manly	cronulla	$1.45	$2.75	No Data	No Data	No Data	No Data	No Data	1
7	wests	sgeorge	$2.08	$1.75	No Data	No Data	No Data	No Data	No Data	1
8	brisbane	penrith	$1.43	$2.85	No Data	No Data	No Data	No Data	No Data	1
9	melbourne	nzealand	$1.28	$3.70	No Data	No Data	No Data	No Data	No Data	1
10	souths	canterbury	$2.00	$1.82	down	0	-14	0	-8	1
11	sydney	brisbane	$1.68	$2.20	down	2	14	2	36	1
12	newcastle	manly	$2.35	$1.60	up	2	16	0	-6	1
13	penrith	canberra	$1.58	$2.40	down	0	-36	0	-16	1
14	nqld	wests	$1.33	$3.35	down	0	-18	2	8	1
15	nzealand	parramatta	$2.00	$1.82	down	0	-14	2	8	1
16	melbourne	cronulla	$1.36	$3.20	up	2	14	2	6	1
17	sgeorge	gcoast	$1.65	$2.25	down	0	-8	2	18	1
18	brisbane	nqld	$1.48	$2.65	up	4	42	0	-38	1
19	parramatta	newcastle	$1.38	$3.10	down	2	-6	4	17	1
20	sydney	melbourne	$2.00	$1.82	down	2	8	2	13	1
21	canberra	sgeorge	$1.82	$2.00	down	2	-12	2	10	1
22	gcoast	cronulla	$1.82	$2.00	down	2	0	4	7	1
23	canterbury	wests	$2.15	$1.72	up	2	30	4	28	1
24	penrith	souths	$1.82	$2.00	up	0	-40	0	-52	1
25	manly	nzealand	$1.54	$2.50	down	0	-7	2	0	1
26	canterbury	sydney	$2.40	$1.58	No Data	No Data	No Data	No Data	No Data	2
27	melbourne	brisbane	$1.62	$2.30	No Data	No Data	No Data	No Data	No Data	2
28	nqld	parramatta	$2.05	$1.78	No Data	No Data	No Data	No Data	No Data	2
29	sgeorge	cronulla	$1.72	$2.15	No Data	No Data	No Data	No Data	No Data	2
30	gcoast	canberra	$1.35	$3.25	No Data	No Data	No Data	No Data	No Data	2
31	nzealand	newcastle	$1.54	$2.50	No Data	No Data	No Data	No Data	No Data	2
32	manly	souths	$1.20	$4.65	No Data	No Data	No Data	No Data	No Data	2
33	wests	penrith	$1.50	$2.60	No Data	No Data	No Data	No Data	No Data	2

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E2 =IF(AQ2<>"No Data",IF(OR(AQ2>AS2,AR2>AT2),"up",IF(OR(AS2>AQ2,AT2>AR2),"down","up")),"No Data") AQ2 =IF(AI2="nd","No Data",SUM((AI2*2)+SUM(AJ2*1))) AR2 =IF(AI2="nd","No Data",SUM((AM2-AN2))) AS2 =IF(AK2="nd","No Data",SUM((AK2*2)+SUM(AL2*1))) AT2 =IF(AK2="nd","No Data",SUM((AO2-AP2))) <tbody> </tbody>

<tbody>
</tbody>

 

[Marcelo Branco]

Sorry, my bad, i had misunderstood the problem

Maybe this

=IF(AQ2<>"No Data",IF(AND(AQ2=AS2,AR2=AT2),"up",IF(AQ2>AS2,"up",IF(AS2>AQ2,"down",IF(AR2>AT2,"up","down")))),"No Data")

M.