Hi guys,
Can someone assist with a formula by which each winning team regardless if is playing home or away can be automatically identified by the final result (columns F and G) with highlighting the cell in a different colour, let's say in blue?
Thanks and appreciate it in advance.
Thank you all.
Can someone assist with a formula by which each winning team regardless if is playing home or away can be automatically identified by the final result (columns F and G) with highlighting the cell in a different colour, let's say in blue?
Soccer League v2.xlsx | ||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | |||
3 | Round | Result | Total Goals | |||||||||||||||||||||||||||||||||||||
4 | Date | Home | 1st Half | 2nd Half | Away | POINTS | 45' | 90' | KT | 1 PP | 2 PP | 0 - 1 | 0 - 2 | 2 - 3 | 2 - 4 | 3 - 5 | 4 - 6 | 2+ | 3+ | 4+ | 5+ | 7+ | GG | GG3+ | GG4+ | 1 GG | 2 GG | G 1PP | G 2PP | VG | X 1PP | OG D/G | DP | 2+ 1PP | 2+ 2PP | |||||
5 | 05.08.2022 | Eintracht Frankfurt | 0 | 5 | 1 | 6 | Bayern Munich | 0 | 3 | 2 | 2 | 2 | 5 | 2 | 2+ | 3+ | 4+ | 5+ | 7+ | GG | GG3+ | GG4+ | 2GG | 3+ | 2+ | 1>2 | GG3+ | DP | 2+ 1PP | 2+ 2PP | ||||||||||
6 | 06.08.2022 | Wolfsburg | 1 | 2 | 2 | 2 | Werder Bremen | 1 | 1 | 2 | X | X | 3 | 1 | 2-4 | 3-5 | 4-6 | 2+ | 3+ | 4+ | GG | GG3+ | GG4+ | 1GG | 2GG | 3+ | 1+ | 1>2 | GG3+ | 2+ 1PP | 2+ 2PP | |||||||||
7 | 06.08.2022 | Union Berlin | 1 | 0 | 3 | 1 | Hertha Berlin | 3 | 0 | 1 | 1 | 1 | 1 | 3 | 2-4 | 3-5 | 4-6 | 2+ | 3+ | 4+ | GG | GG3+ | GG4+ | 2GG | 1+ | 3+ | 2>1 | GG3+ | DP | 2+ 2PP | ||||||||||
8 | 06.08.2022 | Bochum | 1 | 1 | 1 | 2 | Mainz | 0 | 3 | X | 2 | 2 | 2 | 1 | 2-3 | 2-4 | 3-5 | 2+ | 3+ | GG | GG3+ | 1GG | 2GG | 2+ | 1+ | 1>2 | X | GG3+ | 2+ 1PP | 2+ 2PP | ||||||||||
9 | 06.08.2022 | B. Monchengladbach | 1 | 1 | 3 | 1 | Hoffenheim | 3 | 0 | X | 1 | 1 | 2 | 2 | 2-4 | 3-5 | 4-6 | 2+ | 3+ | 4+ | GG | GG3+ | GG4+ | 1GG | 2GG | 2+ | 2+ | 1=2 | X | GG3+ | 2+ 1PP | 2+ 2PP | ||||||||
10 | 06.08.2022 | Augsburg | 0 | 0 | 0 | 4 | Freiburg | 0 | 3 | X | 2 | 2 | 0 | 4 | 2-4 | 3-5 | 4-6 | 2+ | 3+ | 4+ | 1+ | 3+ | 2>1 | X | DNDG | 2+ 2PP | ||||||||||||||
11 | 06.08.2022 | Dortmund | 1 | 0 | 1 | 0 | Bayer Leverkusen | 3 | 0 | 1 | 1 | 1 | 1 | 0 | 0-1 | 0-2 | 1+ | NG | 1>2 | GNDG | ||||||||||||||||||||
12 | 07.08.2022 | Stuttgart | 1 | 1 | 1 | 1 | RB Leipzig | 1 | 1 | X | X | X | 2 | 0 | 0-2 | 2-3 | 2-4 | 2+ | GG | 1GG | 2GG | 2+ | NG | 1>2 | X | GG | 2+ 1PP | 2+ 2PP | ||||||||||||
13 | 07.08.2022 | FC Koln | 0 | 0 | 3 | 1 | Schalke | 3 | 0 | X | 1 | 1 | 0 | 4 | 2-4 | 3-5 | 4-6 | 2+ | 3+ | 4+ | GG | GG3+ | GG4+ | 2GG | 1+ | 3+ | 2>1 | X | GG3+ | 2+ 2PP | ||||||||||
Bunde League |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I5:I13 | I5 | =IF(F5="","",IF(F5>G5,3,IF(F5=G5,1,0))) |
J5:J13 | J5 | =IF(G5="","",IF(G5>F5,3,IF(G5=F5,1,0))) |
K5:K13 | K5 | =IF(D5=""," ",IF(D5-E5>0,1,IF(D5-E5<0,2,"X"))) |
L5:L13 | L5 | =IF(F5=""," ",IF(F5-G5>0,1,IF(F5-G5<0,2,"X"))) |
M5:M13 | M5 | =IF(F5="","",IF(F5=G5,"X",IF(F5<G5,2,IF(F5>G5,1,0)))) |
N5:N13 | N5 | =IF(AND(D5<>"",E5<>""),D5+E5,"") |
O5:O13 | O5 | =IF(AND(F5<>"",G5<>""),(F5+G5)-(D5+E5),"") |
P5:P13 | P5 | =IF(AND(F5<>"",G5<>"",F5+G5<=1),"0-1","") |
Q5:Q13 | Q5 | =IF(AND(F5<>"",G5<>"",F5+G5<=2),"0-2","") |
R5:R13 | R5 | =IF(F5+G5=2,"2-3",IF(F5+G5=3,"2-3","")) |
S5:S13 | S5 | =IF(F5+G5=2,"2-4",IF(F5+G5=3,"2-4",IF(F5+G5=4,"2-4",""))) |
T5:T13 | T5 | =IF(F5+G5=3,"3-5",IF(F5+G5=4,"3-5",IF(F5+G5=5,"3-5",""))) |
U5:U13 | U5 | =IF(F5+G5=4,"4-6",IF(F5+G5=5,"4-6",IF(F5+G5=6,"4-6",""))) |
V5:V13 | V5 | =IF(F5+G5="","",IF(F5+G5>=2,"2+","")) |
W5:W13 | W5 | =IF(F5+G5="","",IF(F5+G5>=3,"3+","")) |
X5:X13 | X5 | =IF(F5+G5="","",IF(F5+G5>=4,"4+","")) |
Y5:Y13 | Y5 | =IF(F5+G5="","",IF(F5+G5>=5,"5+","")) |
Z5:Z13 | Z5 | =IF(F5+G5="","",IF(F5+G5>=7,"7+","")) |
AA5:AA13 | AA5 | =IF(AND(F5>0,G5>0),"GG","") |
AB5:AB13 | AB5 | =IF(AND(D5+F5>0,E5+G5>0,F5+G5>2),"GG3+","") |
AC5:AC13 | AC5 | =IF(AND(D5+F5>0,E5+G5>0,F5+G5>3),"GG4+","") |
AD5:AD13 | AD5 | =IF(AND(D5>0,E5>0),"1GG","") |
AE5:AE13 | AE5 | =IF(AND(F5>0,G5>0),"2GG","") |
AF5:AF13 | AF5 | =IF(N5="","",IF(113<1,"NG",IF(N5<2,"1+",IF(N5<3,"2+","3+")))) |
AG5:AG13 | AG5 | =IF(O5="","",IF(O5<1,"NG",IF(O5<2,"1+",IF(O5<3,"2+","3+")))) |
AH5:AH13 | AH5 | =IF(AND(N5<>"",O5<>""),IF(N5<O5,"2>1",IF(N5>O5,"1>2","1=2")),"") |
AI5:AI13 | AI5 | =IF(AND(D5<>"",E5<>"",D5=E5),"X","") |
AJ5:AJ13 | AJ5 | =IF(N5="","",IF(F5=0,IF(G5=0,"NG","DNDG"),IF(F5>0,IF(G5>0,IF(F5+G5>=3,"GG3+","GG"),"GNDG"),"DNDG"))) |
AK5:AK13 | AK5 | =IF(OR(AND(D5>E5,F5>G5,F5>D5),AND(E5>D5,G5>F5,G5>E5)),"DP","") |
AL5:AL13 | AL5 | =IF(D5+E5>=2,"2+ 1PP","") |
AM5:AM13 | AM5 | =IF(F5+G5>=2,"2+ 2PP","") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
H5:H13 | Cell Value | duplicates | text | NO |
C5:C13 | Cell Value | duplicates | text | NO |
Thanks and appreciate it in advance.
Thank you all.