conditional formatting formula for two-leg match tie-breaker

V

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
 
Sort by date Sort by votes
kevin9999 said:
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?
Click to expand...
I'm sorry, perhaps I wasn't clear enough when I made the request. The formulas are ok. They work perfectly. Can you please clarify if I have to use each of these formulas for each particular case that you exemplified? The problem I have is that I have multiple worksheets with multiple types of scoring sets so, the idea is to use a standard set of formulas for all the different scoring scenarios...
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Thanks for the more detailed explanation of your requirement. Unfortunately that's a bit beyond my capabilities. Hopefully someone else on the forum can provide the answer you seek.
 
Upvote 0
Not sure what you mean by different scoring scenarios, but this is what I ended up with using a single formula on different 'tables' of data. It's about at my limit though...
Book1
ABCDEFG
1AAA30
2BBB12
3CCC03
4DDD21
5EEE11GGG22
6FFF11HHH12
7III30
8JJJ12
9KKK03
10LLL21
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E5:E10Expression=IF(ISODD(ROW(E5)),OR(SUM(OFFSET(E5,0,1,1,2))>SUM(OFFSET(E5,1,1,1,2)),AND(SUM(OFFSET(E5,0,1,1,2))=SUM(OFFSET(E5,1,1,1,2)),OFFSET(E5,0,2,1,1)>OFFSET(E5,1,1,1,1))),OR(SUM(OFFSET(E5,0,1,1,2))>SUM(OFFSET(E5,-1,1,1,2)),AND(SUM(OFFSET(E5,0,1,1,2))=SUM(OFFSET(E5,-1,1,1,2)),OFFSET(E5,0,1,1,1)>OFFSET(E5,-1,2,1,1))))textNO
A1:A8Expression=IF(ISODD(ROW(A1)),OR(SUM(OFFSET(A1,0,1,1,2))>SUM(OFFSET(A1,1,1,1,2)),AND(SUM(OFFSET(A1,0,1,1,2))=SUM(OFFSET(A1,1,1,1,2)),OFFSET(A1,0,2,1,1)>OFFSET(A1,1,1,1,1))),OR(SUM(OFFSET(A1,0,1,1,2))>SUM(OFFSET(A1,-1,1,1,2)),AND(SUM(OFFSET(A1,0,1,1,2))=SUM(OFFSET(A1,-1,1,1,2)),OFFSET(A1,0,1,1,1)>OFFSET(A1,-1,2,1,1))))textNO
 
Upvote 0
Thanks! Can you please post the mini-sheet so that I can download it and better understand how it works in all these different scenarios? I'm sorry I'm a bit of a novice.
 
Upvote 0
@valmir
Can you show us a slightly larger sample of what the layout of your data actually is (with XL2BB again please)?
Is there anything on the sheet that indicates which is Home team and which is Away team? If so, include that on your mini sheet.
 
Upvote 0
Peter_SSs said:
@valmir
Can you show us a slightly larger sample of what the layout of your data actually is (with XL2BB again please)?
Is there anything on the sheet that indicates which is Home team and which is Away team? If so, include that on your mini sheet.
Click to expand...
Stats 1987 - Copy.xlsx
CDEFGILMNOQRSTUVWXYZAAABAC
1ROUND OF 3217 mai31 maiROUND OF 16
21PRI73
3ICU2116 jul26 jul
4PRI11
516 mai30 maiINT03
62INT22
7IHU046 set20 set
8INT303
917 mai31 maiPET325
103SAS10
11PET2616 jul26 jul
12PET0141
1317 mai31 maiDBG1031
144DBG1210 out24 out
15MAM015PET415
1617 mai30 mai1FCC101
175FCC60
18DMX0015 jul26 jul
19FCC022
2017 mai30 maiPHU022
21615F005 set20 set
22PHU0112FCC022
237VIT001DCS101
24DCS0326 jul8 ago
25DCS101
260 janLEO000
278KCS0011 nov
28LEO214PET4
299ICN111FHL1
30PRO2314 jul25 jul
3117 mai30 maiPRO246
3219 mai31 maiDTA336
3310DTA315 set20 set
349JAN101PRO101
3511MLN002PRM112
36PRM4215 jul25 jul
3731 mai24 junPRM224
3817 mai31 maiDCH123
3912DCH3114 out25 out
40FNB201PRM101
413FHL033
4213DBE10
43CAM3113 jul25 jul
44CAM202
4517 mai30 maiUSB044
4614EVE23
47USB316 set20 set
483USB123
4931 mai8FHL448
5015COU11
51FHL3013 jul26 jul
52FHL123
5317 mai31 maiSAG033
541611N02
55SAG24
AC
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E1:F1,E5:F5,E9:F9,E13:F13,E16:F16,E20:F20,E25:F25,E31:F32,E37:F38,E41:F41,E45:F45,E49:F49,E53:F53Cell Value=0textNO
G2:G3,G6:G7,G10:G11,G14:G15,G17:G18,G39:G40,G42:G43,G46:G47,G50:G51,G54:G55,O52:O53,O44:O45,O37:O38,O31:O32,O25:O26,O19:O20,O12:O13,O4:O5,U8:U9,U22:U23,U34:U35,U48:U49,Z15:Z16,Z40:Z41,AD28:AD29,I2:I3,I6:I7,I10:I11,I14:I15,I17:I18,G21:G24,I21:I24,G27:G30Cell Value=0textNO
G35:G36,I35:I36Cell Value=0textNO
G33:G34,I33:I34Cell Value=0textNO
AB28Expression=AE28="**"textNO
AB29Expression=AE29="**"textNO
AB29Expression=AE29="*"textNO
AB28Expression=AE28="*"textNO
AB29Expression=AND(AE29>AE28;ISNUMBER(AE28);ISNUMBER(AE29))textNO
AB28Expression=AND(AE28>AE29;ISNUMBER(AE28);ISNUMBER(AE29))textNO
AB29Cell Value=0textNO
AB28Cell Value=0textNO
AB29Expression=AD29="Đ"textNO
AB28Expression=AD28="Đ"textNO
AB29Expression=AC29="Đ"textNO
AB28Expression=AC28="Đ"textNO
AB29Expression=AND(AA29>AA28;AC29<>"Đ";AD29<>"Đ")textNO
AB28Expression=AND(AA28>AA29;AC28<>"Đ";AD28<>"Đ")textNO
Y39Cell Value=0textNO
D3,D7,D11,D15,D18,D22,D24,D28,D30,D34,D36,D40,D43,D47,D51,D55,L5,L13,L20,L26,L32,L38,L45,L53,R9,R23,R35,R49,W16,W41Expression=G3="**"textNO
D2,D6,D10,D14,D17,D21,D23,D27,D29,D33,D35,D39,D42,D46,D50,D54,L4,L12,L19,L25,L31,L37,L44,L52,R8,R22,R34,R48,W15,W40Expression=G2="**"textNO
D3,D7,D11,D15,D18,D22,D24,D28,D30,D34,D36,D40,D43,D47,D51,D55,L5,L13,L20,L26,L32,L38,L45,L53,R9,R23,R35,R49,W16,W41Expression=AND(G3>G2;ISNUMBER(G2);ISNUMBER(G3))textNO
D2,D6,D10,D14,D17,D21,D23,D27,D29,D33,D35,D39,D42,D46,D50,D54,L4,L12,L19,L25,L31,L37,L44,L52,R8,R22,R34,R48,W15,W40Expression=AND(G2>G3;ISNUMBER(G2);ISNUMBER(G3))textNO
D3,D7,D11,D15,D18,D22,D24,D28,D30,D34,D36,D40,D43,D47,D51,D55,L5,L13,L20,L26,L32,L38,L45,L53,R9,R23,R35,R49,W16,W41Cell Value=0textNO
D2,D6,D10,D14,D17,D21,D23,D27,D29,D33,D35,D39,D42,D46,D50,D54,L4,L12,L19,L25,L31,L37,L44,L52,R8,R22,R34,R48,W15,W40Cell Value=0textNO
D3,D7,D11,D15,D18,D22,D24,D28,D30,D34,D36,D40,D43,D47,D51,D55,L5,L13,L20,L26,L32,L38,L45,L53,R9,R23,R35,R49,W16,W41Expression=F3="Đ"textNO
D2,D6,D10,D14,D17,D21,D23,D27,D29,D33,D35,D39,D42,D46,D50,D54,L4,L12,L19,L25,L31,L37,L44,L52,R8,R22,R34,R48,W15,W40Expression=F2="Đ"textNO
D3,D7,D11,D15,D18,D22,D24,D28,D30,D34,D36,D40,D43,D47,D51,D55,L5,L13,L20,L26,L32,L38,L45,L53,R9,R23,R35,R49,W16,W41Expression=AND(H3>H2;E3<>"Đ";F3<>"Đ")textNO
D2,D6,D10,D14,D17,D21,D23,D27,D29,D33,D35,D39,D42,D46,D50,D54,L4,L12,L19,L25,L31,L37,L44,L52,R8,R22,R34,R48,W15,W40Expression=AND(H2>H3;E2<>"Đ";F2<>"Đ")textNO
AA28:AA29Cell Value=0textNO
V40:V41Cell Value=0textNO
V15:V16Cell Value=0textNO
Q48:Q49Cell Value=0textNO
Q34:Q35Cell Value=0textNO
Q22:Q23Cell Value=0textNO
Q9Cell Value=0textNO
C54:C55,C50:C51,C46:C47,C42:C43,C39:C40,C33:C36,C27:C30,C21:C24,C17:C18,C14:C15,C10:C11,C6:C7Cell Value=0textNO
X39Cell Value=0textNO
X14:Y14Cell Value=0textNO
S47:T47Cell Value=0textNO
S33:T33Cell Value=0textNO
S7:T7Cell Value=0textNO
R7Cell Value=0textNO
R21:T21Cell Value=0textNO
R33Cell Value=0textNO
R47Cell Value=0textNO
F1Cell Value=0textNO
F16Cell Value=0textNO
M3:N3Cell Value=0textNO
M18:N18Cell Value=0textNO
M43:N43Cell Value=0textNO
M51:N51Cell Value=0textNO
M11:N11Cell Value=0textNO
M24:N24Cell Value=0textNO
M36:N36Cell Value=0textNO
M30:N30Cell Value=0textNO
AC27Cell Value=0textNO
C2:C3Cell Value=0textNO
L51Cell Value=0textNO
L43Cell Value=0textNO
L36Cell Value=0textNO
L30Cell Value=0textNO
L24Cell Value=0textNO
L18Cell Value=0textNO
L11Cell Value=0textNO
L3,F37:F38,F56,F52:F53,F44:F45,F4:F5,F48:F49,F19:F20,F31:F32,F8:F9,F25:F26,F12:F13,F41,AH1:AI5,AH21:AI25,AH29:AI33,AH37:AI41,AH45:AI49,AH53:AI57,AH61:AI65,AH69:AI73,AH77:AI81,AH85:AI89,AH93:AI97,AH101:AI105,AH109:AI113,AH117:AI121,AH125:AI126,AH13:AI17Cell Value=0textNO
 
Upvote 0
The home team in the first leg is always the upper left cell, the home team in the second leg is always the bottom right cell. As you can see, I am using the same set of formulas across all of the matches and I was able to figure out everything except for those cases where the away goal rule applies. In this particular worksheet, I have four different such cases: D6:F7; L19:N20; L31:N32; L52:N53, that I am struggling with. The idea is to incorporate this "away-goal-rule" formula in all cells because when I create a new worksheet, I just want to copy and paste the set of formulas to the new worksheet
 
Upvote 0
Thanks for the extra data. Why is PET green?

1689587939554.png


In other words, what should happen when total goals are equal and away goals are equal?
 
Upvote 0
Peter_SSs said:
Thanks for the extra data. Why is PET green?

View attachment 95381

In other words, what should happen when total goals are equal and away goals are equal?
Click to expand...
as you can see, that's a darker green, to differentiate in case each team wins one game by exactly the same score. this is the case where the qualification is determined by penalty kicks (O12 and O13). I guess I already have a formula for this particular case.
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Extract and Merge Files
Replies
1
Views
110
rowebca
rowebca
S
  • Question Question
Index Match with multiple conditions including dates
Replies
1
Views
60
StephenCrump
S
M
  • Question Question
VBA Code or Formula to get all combinations of 2 cells on each row
Replies
3
Views
1K
Eric W
Eric W
I
  • Solved
FILTER formula criteria based of a variable with VBA
Replies
2
Views
274
ItalianPlatinum
I
F
  • Question Question
Scoresheet creation for a trivia game
Replies
1
Views
337
Rijnsent
Rijnsent

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

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