I need help with formulas for away/home

TLS49

Board Regular
Joined
Nov 26, 2019
Messages
74
Office Version
  1. 365
Platform
  1. Windows
away/home that are in color of orange in conference & division area



NEW NFL SCHEDULES.xlsb
ABCDEFGHIJKLMNOPQR
1HOME
2CONFERENCE RESUATS
3
4TEAMCONFERENCEDIVISIONWINS3WIN %100%WINS2WIN %100.00%
5BILLSAFCEASTLOSSES0LOSSES0
6TIES0TIES0
7
8TEAM POINTSOPPONENT POINTSDIFFERENCETEAM POINTSOPPONENT POINTSDIFFERENCE
9937716584513
10
11TEAM POINTS %OPPONENTS POINTS %DIFFERENCE %TEAM POINTS %OPPONENTS POINTS %DIFFERENCE %
1254.71%45.29%9.41%56.31%43.69%12.62%
13
14TEAM AVGOPPONENTS AVGDIFFERENCE AVGTEAM AVGOPPONENTS AVGDIFFERENCE AVG
1531.00025.6675.33329.00022.5006.500
16
17WEEKWIN,LOS,TIESSCOREOPPONENTSAWAY/HOMEOPPONENT CONFERENCEOPPONENT DIVISIONOPPONENT SCOREATWIN,LOS,TIESSCOREOPPONENTSAWAY/HOMEOPPONENT CONFERENCEOPPONENT DIVISIONOPPONENT SCOREAT
181W27JETSHOMEAFCEAST17 W27JETSAFCEAST17 
192W31DOLPHINSAWAYAFCEAST28@W31DOLPHINSAFCEAST28@
203W35RAMSHOMENFCWEST32        
214 RAIDERSAWAYAFCWEST@      @
225 TITANSAWAYAFCSOUTH@      @
236 CHIEFSHOMEAFCWEST        
247 JETSAWAYAFCEAST@      @
258 PATRIOTSHOMEAFCEAST        
269 SEAHAWKSHOMENFCWEST        
2710 CARDINALSAWAYNFCWEST@       
2811 BYE           
2912 CHARGERSHOMEAFCWEST        
3013 49ERSAWAYNFCWEST@       
3114 STEELERSHOMEAFCNORTH        
3215 BRONCOSAWAYAFCWEST@      @
3316 PATRIOTSAWAYAFCEAST@      @
3417 DOLPHINSHOMEAFCEAST        
35
36
37DIVISION RESUATS
38
39WINS0WIN %NO GAME PLAYED
40LOSSES0
41TIES0
42
43TEAM POINTSOPPONENT POINTSDIFFERENCE
44584513
45
46TEAM POINTS %OPPONENTS POINTS %DIFFERENCE %
4756.31%43.69%12.62%
48
49TEAM AVGOPPONENTS AVGDIFFERENCE AVG
50   
51
52WIN,LOS,TIESSCOREOPPONENTSAWAY/HOMEOPPONENT CONFERENCEOPPONENT DIVISIONOPPONENT SCOREAT
53W27JETSAFCEAST17 
54W31DOLPHINSAFCEAST28@
55       
56       
57       
58       
59      @
60       
61       
62       
63       
64       
65       
66       
67       
68      @
BILLS
Cell Formulas
RangeFormula
H4,O39,O4H4=IFERROR((F4+(F6*0.5))/(F4+F5+F6),"NO GAME PLAYED")
B5B5=IFERROR(VLOOKUP(A5,'TEAMS INFOS'!B4:D35,2,0),"")
C5C5=IFERROR(VLOOKUP(A5,'TEAMS INFOS'!B4:D35,3,0),"")
F4F4=COUNTIF($B$18:$B$34,"W")
F5F5=COUNTIF($B$18:$B$34,"L")
F6F6=COUNTIF($B$18:$B$34,"T")
M4M4=COUNTIF($K$18:$K$34,"W")
M5M5=COUNTIF($K$18:$K$34,"L")
M6M6=COUNTIF($K$18:$K$34,"T")
B9,K44,K9B9=SUM(C18:C34)
D9D9=SUM($H$18:$H$34)
F9,O50,O47,O44,O15,F15,O12,F12,O9F9=IFERROR(B9-D9,"")
M9,M44M9=SUM(Q18:Q34)
B12,K47,K12B12=IFERROR(B9/(B9+D9),"")
D12,M47,M12D12=IFERROR(D9/(B9+D9),"")
B15B15=IFERROR(B9/(F4+F5+F6),"")
D15,K50,K15D15=IFERROR(D9/(F4+F5+F6),"")
M15,M50M15=IFERROR(M9/(M4+M5+M6),"")
D18:E34D18='SETUP SCHEDULES'!F6
F18:F34,O53:O68,O18:O34F18=IFERROR(VLOOKUP(D18,'TEAMS INFOS'!$B$4:$D$35,2,0),"")
G18:G34,P53:P68,P18:P34G18=IFERROR(VLOOKUP(D18,'TEAMS INFOS'!$B$4:$D$35,3,0),"")
K18:K34,K53:K68K18=IF(AND(L18="",Q18=""),"",IF(L18=Q18,"T",IF(L18>Q18,"W","L")))
L18:L34L18=IF(AND(C18="",H18=""),"",IF($B$5=F18,C18,""))
M18:M34M18=IF(AND(C18="",H18=""),"",IF($B$5=F18,D18,""))
Q18:Q34Q18=IF(AND(C18="",H18=""),"",IF($B$5=F18,H18,""))
R18:R34R18=IF($B$5=F18,IF(E18="AWAY","@",""),"")
B18:B34B18=IFERROR(IF(AND(C18="",H18=""),"",IF(C18=H18,"T",IF(C18>H18,"W","L"))),"")
I18:I34I18=IF(D18="BYE","",IF(E18="AWAY","@",""))
L53:L68L53=IF(AND(C18="",H18=""),"",IF(AND(F18="",G18=""),"",IF(AND($B$5=F18,$C$5=G18),C18,"")))
M53:M68M53=IF(AND(C18="",H18=""),"",IF(AND(F18="",G18=""),"",IF(AND($B$5=F18,$C$5=G18),D18,"")))
Q53:Q68Q53=IF(AND(C18="",H18=""),"",IF(AND(F18="",G18=""),"",IF(AND($B$5=F18,$C$5=G18),H18,"")))
R53:R68R53=IF(AND($B$5=F18,$C$5=G18),IF(E18="AWAY","@",""),"")




Thanks you

Thomas
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

TLS49

Board Regular
Joined
Nov 26, 2019
Messages
74
Office Version
  1. 365
Platform
  1. Windows
The orange selection is in Column N

Thanks You

Thomas
 

TLS49

Board Regular
Joined
Nov 26, 2019
Messages
74
Office Version
  1. 365
Platform
  1. Windows
You can look at cells R18 & R53 to get idea I want happen.

if statement in cells N18:N33 get data from E18:F34 & N53:N68 get data from E18:F35 to put in away or home in cells N18:N33 & N53:N68

Thanks You

Thomas
 

TLS49

Board Regular
Joined
Nov 26, 2019
Messages
74
Office Version
  1. 365
Platform
  1. Windows
Excel Formula:
N18:N33
=IF($B$5=F18,IF(E18="AWAY","@",""),"")=IF($B$5=F18,IF(E18="HOME",""),"")





N53:n69
=IF($C$5=F18,IF(E18="AWAY","@",""),"")=IF($C$5=F18,IF(E18="HOME",""),"")



Should put away or home from E18E34

I need help with this

Thanks you

Thomas
 

Watch MrExcel Video

Forum statistics

Threads
1,114,253
Messages
5,546,774
Members
410,757
Latest member
jonni
Top