conditional formatting formula for two-leg match tie-breaker

[valmir]

Hello everyone
I have attached a file with two teams. Team AAA won the first leg 3-1, team BBB won the second leg 2-0. Both teams ended up with a +2 goal difference. However, team BBB had the advantage of having 1 goal scored compared to 0 goal of AAA (as away teams) so, basically we will have a first condition which is if the sum of goals scored by each team is equal, then the second condition applies to the goals scored as an away team.
So the formula would be something like:
formula for A1
(B1+C1)=(B2+C2) and C1>B2
formula for A2
(B1+C1)=(B2+C2) and B2>C1
I want each formula to highlight green whichever team meets the criteria, in this case A2 (BBB)

Book1
	A	B	C
1	AAA	3	0
2	BBB	1	2
Sheet1

 

[awoohaw]

There is probably a more elegant solution, but try this:

I needed to add an indicator for who the AWAY team is.
I'll be conditionally formatting in a moment.

Book1
	A	B	C
1
2	AAA	3	0
3	BBB	1	2
4	AWAY	BBB	AAA
5
6	Winner	BBB
Valmir

Cell Formulas
Range	Formula
B6	B6	= IF(SUM(B2:C2)>SUM(B3:C3),A2,IF(SUM(B2:C2)<SUM(B3:C3),A3, IF(SUM((B$4=$A$2:$A$3)*(C$2:C$3))>SUM((B$4=$A$2:$A$3)*(B$2:B$3)),B$4, IF(SUM((B$4=$A$2:$A$3)*(B$2:B$3))<SUM((B$4=$A$2:$A$3)*(B$2:B$3)),B$4,"Draw"))))

 

[valmir]

IF(SUM(B2:C2)>SUM(B3:C3),A2,IF(SUM(B2:C2)<SUM(B3:C3),A3, IF(SUM((B$4=$A$2:$A$3)*(C$2:C$3))>SUM((B$4=$A$2:$A$3)*(B$2:B$3)),B$4, IF(SUM((B$4=$A$2:$A$3)*(B$2:B$3))<SUM((B$4=$A$2:$A$3)*(B$2:B$3)),B$4,"Draw"))))

ok thanks. Waiting for your kind reply!

 

[valmir]

Please note that for the first leg BBB is the away team and AAA is the away team in the second leg, meaning that BBB in the first leg scored 1 and AAA in the second leg didn't score that's why BBB moves forward!

 

[awoohaw]

Please note that for the first leg BBB is the away team and AAA is the away team in the second leg, meaning that BBB in the first leg scored 1 and AAA in the second leg didn't score that's why BBB moves forward!

i think that is what my formula does. If it does not, please let me know. As I think it displays BBB as winning in your scenario.

 

[awoohaw]

argh. I'm drawing blanks on getting the CF correct. I need to take a break from this for a while. My apologies.

 

[valmir]

argh. I'm drawing blanks on getting the CF correct. I need to take a break from this for a while. My apologies.

no problem! thanks for trying!

 

[kevin9999]

It's a bit 'icky', but see if this gives you what you want:

Book1
	A	B	C
1	AAA	3	0
2	BBB	1	2
3
4	AAA	0	3
5	BBB	2	1
6
7	AAA	1	1
8	BBB	1	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A8	Expression	=(B8+C8)>(B7+C7)	text	YES
A7	Expression	=(B7+C7)>(B8+C8)	text	YES
A8	Expression	=B8>C7	text	YES
A7	Expression	=C7>B8	text	YES
A5	Expression	=(B5+C5)>(B4+C4)	text	YES
A4	Expression	=(B4+C4)>(B5+C5)	text	YES
A5	Expression	=B5>C4	text	YES
A4	Expression	=C4>B5	text	YES
A2	Expression	=(B2+C2)>(B1+C1)	text	YES
A1	Expression	=(B1+C1)>(B2+C2)	text	YES
A2	Expression	=B2>C1	text	YES
A1	Expression	=C1>B2	text	YES

 

[valmir]

It's a bit 'icky', but see if this gives you what you want:

Book1
	A	B	C
1	AAA	3	0
2	BBB	1	2
3
4	AAA	0	3
5	BBB	2	1
6
7	AAA	1	1
8	BBB	1	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A8	Expression	=(B8+C8)>(B7+C7)	text	YES
A7	Expression	=(B7+C7)>(B8+C8)	text	YES
A8	Expression	=B8>C7	text	YES
A7	Expression	=C7>B8	text	YES
A5	Expression	=(B5+C5)>(B4+C4)	text	YES
A4	Expression	=(B4+C4)>(B5+C5)	text	YES
A5	Expression	=B5>C4	text	YES
A4	Expression	=C4>B5	text	YES
A2	Expression	=(B2+C2)>(B1+C1)	text	YES
A1	Expression	=(B1+C1)>(B2+C2)	text	YES
A2	Expression	=B2>C1	text	YES
A1	Expression	=C1>B2	text	YES

Not working. I'm sorry that this seems to be really complicated. I had no idea when I made this request! Thanks for your effort!

 

[kevin9999]

Not working.

My understanding was that you wanted to highlight in green the winner of matches where the result was decided by away goals. If that's not the case then I got your intentions all wrong. If I was right in divining your intentions, then using the sample I provided in post #8, could you please explain what exactly "not working" means?