conditional formatting formula for two-leg match tie-breaker

valmir

Active Member
Joined
Feb 10, 2021
Messages
267
Office Version
  1. 365
Platform
  1. Windows
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
ABC
1AAA30
2BBB12
Sheet1
 
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!
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Exactly which A or B?

1689589261588.png
 
Upvote 0
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
DEF
2PRI73
3ICU21
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3Expression=(E2+F2=E3+F3)*(E3>F2)textNO
D2Expression=(E2+F2=E3+F3)*(F2>E3)textNO


Here is part of my results.

valmir.xlsm
DEFJKLMN
1ROUND OF 16
2PRI73
3ICU21
4PRI11
5INT03
6INT22
7IHU04
8
9
10SAS10
11PET26
12PET01
13DBG10
14DBG12
15MAM01
16
17FCC60
18DMX00
19FCC02
20PHU02
2115F00
22PHU011
23VIT00
24DCS03
25DCS10
26LEO00
27KCS00
28LEO21
29ICN11
30PRO23
31PRO24
32DTA33
33DTA31
349JAN10
35MLN00
36PRM42
37PRM22
38DCH12
Sheet1
 
Upvote 0
Solution
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
DEF
2PRI73
3ICU21
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3Expression=(E2+F2=E3+F3)*(E3>F2)textNO
D2Expression=(E2+F2=E3+F3)*(F2>E3)textNO


Here is part of my results.

valmir.xlsm
DEFJKLMN
1ROUND OF 16
2PRI73
3ICU21
4PRI11
5INT03
6INT22
7IHU04
8
9
10SAS10
11PET26
12PET01
13DBG10
14DBG12
15MAM01
16
17FCC60
18DMX00
19FCC02
20PHU02
2115F00
22PHU011
23VIT00
24DCS03
25DCS10
26LEO00
27KCS00
28LEO21
29ICN11
30PRO23
31PRO24
32DTA33
33DTA31
349JAN10
35MLN00
36PRM42
37PRM22
38DCH12
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!
 
Upvote 0
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. ;)
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top