I need help with formulas for away/home

[TLS49]

away/home that are in color of orange in conference & division area

NEW NFL SCHEDULES.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	HOME
2											CONFERENCE RESUATS
3
4	TEAM	CONFERENCE	DIVISION		WINS	3	WIN %	100%				WINS	2	WIN %	100.00%
5	BILLS	AFC	EAST		LOSSES	0						LOSSES	0
6					TIES	0						TIES	0
7
8		TEAM POINTS		OPPONENT POINTS		DIFFERENCE					TEAM POINTS		OPPONENT POINTS		DIFFERENCE
9		93		77		16					58		45		13
10
11		TEAM POINTS %		OPPONENTS POINTS %		DIFFERENCE %					TEAM POINTS %		OPPONENTS POINTS %		DIFFERENCE %
12		54.71%		45.29%		9.41%					56.31%		43.69%		12.62%
13
14		TEAM AVG		OPPONENTS AVG		DIFFERENCE AVG					TEAM AVG		OPPONENTS AVG		DIFFERENCE AVG
15		31.000		25.667		5.333					29.000		22.500		6.500
16
17	WEEK	WIN,LOS,TIES	SCORE	OPPONENTS	AWAY/HOME	OPPONENT CONFERENCE	OPPONENT DIVISION	OPPONENT SCORE	AT		WIN,LOS,TIES	SCORE	OPPONENTS	AWAY/HOME	OPPONENT CONFERENCE	OPPONENT DIVISION	OPPONENT SCORE	AT
18	1	W	27	JETS	HOME	AFC	EAST	17			W	27	JETS		AFC	EAST	17
19	2	W	31	DOLPHINS	AWAY	AFC	EAST	28	@		W	31	DOLPHINS		AFC	EAST	28	@
20	3	W	35	RAMS	HOME	NFC	WEST	32
21	4			RAIDERS	AWAY	AFC	WEST		@									@
22	5			TITANS	AWAY	AFC	SOUTH		@									@
23	6			CHIEFS	HOME	AFC	WEST
24	7			JETS	AWAY	AFC	EAST		@									@
25	8			PATRIOTS	HOME	AFC	EAST
26	9			SEAHAWKS	HOME	NFC	WEST
27	10			CARDINALS	AWAY	NFC	WEST		@
28	11			BYE
29	12			CHARGERS	HOME	AFC	WEST
30	13			49ERS	AWAY	NFC	WEST		@
31	14			STEELERS	HOME	AFC	NORTH
32	15			BRONCOS	AWAY	AFC	WEST		@									@
33	16			PATRIOTS	AWAY	AFC	EAST		@									@
34	17			DOLPHINS	HOME	AFC	EAST
35
36
37											DIVISION RESUATS
38
39												WINS	0	WIN %	NO GAME PLAYED
40												LOSSES	0
41												TIES	0
42
43											TEAM POINTS		OPPONENT POINTS		DIFFERENCE
44											58		45		13
45
46											TEAM POINTS %		OPPONENTS POINTS %		DIFFERENCE %
47											56.31%		43.69%		12.62%
48
49											TEAM AVG		OPPONENTS AVG		DIFFERENCE AVG
50
51
52											WIN,LOS,TIES	SCORE	OPPONENTS	AWAY/HOME	OPPONENT CONFERENCE	OPPONENT DIVISION	OPPONENT SCORE	AT
53											W	27	JETS		AFC	EAST	17
54											W	31	DOLPHINS		AFC	EAST	28	@
55
56
57
58
59																		@
60
61
62
63
64
65
66
67
68																		@
BILLS

Cell Formulas
Range	Formula
H4,O39,O4	H4	=IFERROR((F4+(F6*0.5))/(F4+F5+F6),"NO GAME PLAYED")
B5	B5	=IFERROR(VLOOKUP(A5,'TEAMS INFOS'!B4:D35,2,0),"")
C5	C5	=IFERROR(VLOOKUP(A5,'TEAMS INFOS'!B4:D35,3,0),"")
F4	F4	=COUNTIF($B$18:$B$34,"W")
F5	F5	=COUNTIF($B$18:$B$34,"L")
F6	F6	=COUNTIF($B$18:$B$34,"T")
M4	M4	=COUNTIF($K$18:$K$34,"W")
M5	M5	=COUNTIF($K$18:$K$34,"L")
M6	M6	=COUNTIF($K$18:$K$34,"T")
B9,K44,K9	B9	=SUM(C18:C34)
D9	D9	=SUM($H$18:$H$34)
F9,O50,O47,O44,O15,F15,O12,F12,O9	F9	=IFERROR(B9-D9,"")
M9,M44	M9	=SUM(Q18:Q34)
B12,K47,K12	B12	=IFERROR(B9/(B9+D9),"")
D12,M47,M12	D12	=IFERROR(D9/(B9+D9),"")
B15	B15	=IFERROR(B9/(F4+F5+F6),"")
D15,K50,K15	D15	=IFERROR(D9/(F4+F5+F6),"")
M15,M50	M15	=IFERROR(M9/(M4+M5+M6),"")
D18:E34	D18	='SETUP SCHEDULES'!F6
F18:F34,O53:O68,O18:O34	F18	=IFERROR(VLOOKUP(D18,'TEAMS INFOS'!$B$4:$D$35,2,0),"")
G18:G34,P53:P68,P18:P34	G18	=IFERROR(VLOOKUP(D18,'TEAMS INFOS'!$B$4:$D$35,3,0),"")
K18:K34,K53:K68	K18	=IF(AND(L18="",Q18=""),"",IF(L18=Q18,"T",IF(L18>Q18,"W","L")))
L18:L34	L18	=IF(AND(C18="",H18=""),"",IF($B$5=F18,C18,""))
M18:M34	M18	=IF(AND(C18="",H18=""),"",IF($B$5=F18,D18,""))
Q18:Q34	Q18	=IF(AND(C18="",H18=""),"",IF($B$5=F18,H18,""))
R18:R34	R18	=IF($B$5=F18,IF(E18="AWAY","@",""),"")
B18:B34	B18	=IFERROR(IF(AND(C18="",H18=""),"",IF(C18=H18,"T",IF(C18>H18,"W","L"))),"")
I18:I34	I18	=IF(D18="BYE","",IF(E18="AWAY","@",""))
L53:L68	L53	=IF(AND(C18="",H18=""),"",IF(AND(F18="",G18=""),"",IF(AND($B$5=F18,$C$5=G18),C18,"")))
M53:M68	M53	=IF(AND(C18="",H18=""),"",IF(AND(F18="",G18=""),"",IF(AND($B$5=F18,$C$5=G18),D18,"")))
Q53:Q68	Q53	=IF(AND(C18="",H18=""),"",IF(AND(F18="",G18=""),"",IF(AND($B$5=F18,$C$5=G18),H18,"")))
R53:R68	R53	=IF(AND($B$5=F18,$C$5=G18),IF(E18="AWAY","@",""),"")

Thanks you

Thomas

 

[TLS49]

The orange selection is in Column N

Thanks You

Thomas

 

[TLS49]

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]

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