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

 

[valmir]

OK, so are you looking for different colours for each of these?

1. Best total goals
2. Best away goals if total goals equal
3. Best penalty kicks if 1 & 2 above are equal

Or would you prefer simply to highlight all winners the same colour no matter how that are evaluated as winner?

exactly!

 

[Peter_SSs]

exactly!

Exactly which A or B?

 

[valmir]

Exactly which A or B?

View attachment 95384

Sorry! Only saw the first part of the question. That would be option A, sir!

 

[Peter_SSs]

Try this

1. Apply the CF as shown below separately to D2 and D3
2. Select D2:D3, double click the format painter and click on the top cell of each subsequent pair.

valmir.xlsm
	D	E	F
2	PRI	7	3
3	ICU	2	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3	Expression	=(E2+F2=E3+F3)*(E3>F2)	text	NO
D2	Expression	=(E2+F2=E3+F3)*(F2>E3)	text	NO

Here is part of my results.

valmir.xlsm
	D	E	F				J	K	L	M	N
1									ROUND OF 16
2	PRI	7	3
3	ICU	2	1
4									PRI	1	1
5									INT	0	3
6	INT	2	2
7	IHU	0	4
8
9
10	SAS	1	0
11	PET	2	6
12									PET	0	1
13									DBG	1	0
14	DBG	1	2
15	MAM	0	1
16
17	FCC	6	0
18	DMX	0	0
19									FCC	0	2
20									PHU	0	2
21	15F	0	0
22	PHU	0	11
23	VIT	0	0
24	DCS	0	3
25									DCS	1	0
26									LEO	0	0
27	KCS	0	0
28	LEO	2	1
29	ICN	1	1
30	PRO	2	3
31									PRO	2	4
32									DTA	3	3
33	DTA	3	1
34	9JAN	1	0
35	MLN	0	0
36	PRM	4	2
37									PRM	2	2
38									DCH	1	2
Sheet1

 

[valmir]

Try this

1. Apply the CF as shown below separately to D2 and D3
2. Select D2:D3, double click the format painter and click on the top cell of each subsequent pair.

valmir.xlsm
	D	E	F
2	PRI	7	3
3	ICU	2	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3	Expression	=(E2+F2=E3+F3)*(E3>F2)	text	NO
D2	Expression	=(E2+F2=E3+F3)*(F2>E3)	text	NO

Here is part of my results.

valmir.xlsm
	D	E	F				J	K	L	M	N
1									ROUND OF 16
2	PRI	7	3
3	ICU	2	1
4									PRI	1	1
5									INT	0	3
6	INT	2	2
7	IHU	0	4
8
9
10	SAS	1	0
11	PET	2	6
12									PET	0	1
13									DBG	1	0
14	DBG	1	2
15	MAM	0	1
16
17	FCC	6	0
18	DMX	0	0
19									FCC	0	2
20									PHU	0	2
21	15F	0	0
22	PHU	0	11
23	VIT	0	0
24	DCS	0	3
25									DCS	1	0
26									LEO	0	0
27	KCS	0	0
28	LEO	2	1
29	ICN	1	1
30	PRO	2	3
31									PRO	2	4
32									DTA	3	3
33	DTA	3	1
34	9JAN	1	0
35	MLN	0	0
36	PRM	4	2
37									PRM	2	2
38									DCH	1	2
Sheet1

The minute I saw this formula, I knew this is exactly what I was looking for. I am just wondering why the previous experts were coming up with such complicated formulas! . Thank you so much for saving my day. All the best!

 

[Peter_SSs]

You're welcome.

I am just wondering why the previous experts were coming up with such complicated formulas!

Probably because they were trying to guess your overall layout & requirement &, like me, were thinking that you wanted a formula to cover all the ways that a team could be evaluated as the winner.
Of course you are very familiar with your data, layout & requirements - we are not.