gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 684
- Office Version
- 2019
- Platform
- Windows
Hello,
Im using the following formula:
=IF(AND(Series="East",Game=1,Team=$Y4),SPRD,"")
working fine in one section.
the second formula is:
=IF(AND(Series="West",Game=1,Team=$Y14),SPRD,"")
is not working in another section?
Sorry for the amount of data.
Where Im pulling the data from:
Below is ALL.
Im using the following formula:
=IF(AND(Series="East",Game=1,Team=$Y4),SPRD,"")
working fine in one section.
the second formula is:
=IF(AND(Series="West",Game=1,Team=$Y14),SPRD,"")
is not working in another section?
Sorry for the amount of data.
NBA.xlsm | |||||
---|---|---|---|---|---|
Y | Z | AA | |||
2 | Eastern | ||||
3 | Team | SPRD | GM1 | ||
4 | MIL | -1.5 | W | ||
5 | ORL | -2 | L | ||
6 | IND | -3.5 | W | ||
7 | MIA | -4 | L | ||
8 | BOS | -5 | W | ||
9 | PHI | -6 | L | ||
10 | TOR | ||||
11 | BRK | ||||
12 | Western | ||||
13 | Team | SPRD | GM1 | ||
14 | LAL | ||||
15 | POR | ||||
16 | HOU | ||||
17 | OKC | ||||
18 | DEN | ||||
19 | UTA | ||||
20 | LAC | #VALUE! | #VALUE! | ||
21 | DAL | #VALUE! | #VALUE! | ||
Playoff Favs |
Cell Formulas | ||
---|---|---|
Range | Formula | |
Z4:Z11 | Z4 | =IF(AND(Series="East",Game=1,Team=$Y4),SPRD,"") |
AA4:AA11 | AA4 | =IF(AND(Series="East",Game=1,Team=$Y4),WL,"") |
Z14:Z21 | Z14 | =IF(AND(Series="West",Game=1,Team=$Y14),SPRD,"") |
AA14:AA21 | AA14 | =IF(AND(Series="West",Game=1,Team=$Y14),WL,"") |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
Game | ='Playoff Favs'!$C$4:$C$19 | Z4:AA4 |
Series | ='Playoff Favs'!$B$4:$B$19 | Z4:AA4 |
SPRD | ='Playoff Favs'!$E$4:$E$19 | Z4:Z11, Z14:Z21 |
Team | ='Playoff Favs'!$F$4:$F$19 | Z4:AA4 |
WL | ='Playoff Favs'!$D$4:$D$19 | AA4:AA11, AA14:AA21 |
Where Im pulling the data from:
NBA.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Copy Teams From "Fav's" | |||||||
2 | reverse | reverse | reverse | ATS | reverse | |||
3 | Section | Series | Game | *W/L* | SPRD | TEAM | ||
4 | Semi | East | 1 | W | -1.5 | MIL | ||
5 | Semi | East | 1 | L | -2 | ORL | ||
6 | Semi | East | 1 | W | -3.5 | IND | ||
7 | Semi | East | 1 | L | -4 | MIA | ||
8 | Semi | East | 1 | W | -5 | BOS | ||
9 | Semi | East | 1 | L | -6 | PHI | ||
10 | Semi | East | 2 | W | -3.5 | TOR | ||
11 | Semi | East | 2 | L | -2 | BRK | ||
12 | Semi | West | 1 | W | -1.5 | LAL | ||
13 | Semi | West | 1 | L | -2 | POR | ||
14 | Semi | West | 2 | W | -3.5 | HOU | ||
15 | Semi | West | 2 | L | -4 | OKC | ||
16 | Semi | West | 1 | W | -5 | DEN | ||
17 | Semi | West | 1 | L | -6 | UTA | ||
18 | Semi | West | 2 | W | -3.5 | LAC | ||
19 | Semi | West | 2 | L | -2 | DAL | ||
Playoff Favs |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A4:A19 | A4 | =IFERROR(VLOOKUP($F4,CHOOSE({1,2,3},Favs!$G$5:$G$28,Favs!$A$5:$A$28,Favs!$H$5:$H$28),2,0),"") |
B4:B19 | B4 | =IFERROR(VLOOKUP($F4,CHOOSE({1,2,3},Favs!$G$5:$G$28,Favs!$B$5:$B$28,Favs!$H$5:$H$28),2,0),"") |
C4:C19 | C4 | =IFERROR(VLOOKUP($F4,CHOOSE({1,2,3},Favs!$G$5:$G$28,Favs!$C$5:$C$28,Favs!$H$5:$H$28),2,0),"") |
E4:E19 | E4 | =IFERROR(VLOOKUP($F4,CHOOSE({1,2,3},Favs!$G$5:$G$28,Favs!$D$5:$D$28,,Favs!$H$5:$H$28),2,0),"") |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
Team | ='Playoff Favs'!$F$4:$F$19 | E4, A4:C4 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
D4:E21 | Cell Value | ="L" | text | NO |
D4:E21 | Cell Value | ="W" | text | NO |
Below is ALL.
Cell Formulas | ||
---|---|---|
Range | Formula | |
A4:A19 | A4 | =IFERROR(VLOOKUP($F4,CHOOSE({1,2,3},Favs!$G$5:$G$28,Favs!$A$5:$A$28,Favs!$H$5:$H$28),2,0),"") |
B4:B19 | B4 | =IFERROR(VLOOKUP($F4,CHOOSE({1,2,3},Favs!$G$5:$G$28,Favs!$B$5:$B$28,Favs!$H$5:$H$28),2,0),"") |
C4:C19 | C4 | =IFERROR(VLOOKUP($F4,CHOOSE({1,2,3},Favs!$G$5:$G$28,Favs!$C$5:$C$28,Favs!$H$5:$H$28),2,0),"") |
G4:G19 | G4 | =IFERROR(VLOOKUP($F4,Playoffs!$A$2:$G$20,7,0),"") |
H4:H19 | H4 | =IFERROR(VLOOKUP($F4,Playoffs!$A$2:$I$20,9,0),"") |
I4:I19 | I4 | =IFERROR(VLOOKUP($F4,Playoffs!$A$2:$L$20,12,0),"") |
J4:J19 | J4 | =IFERROR(VLOOKUP($F4,Playoffs!$A$2:$O$20,15,0),"") |
K4:K19 | K4 | =IFERROR(VLOOKUP($F4,Playoffs!$A$2:$U$20,21,0),"") |
L4:L19 | L4 | =IFERROR(VLOOKUP($F4,Playoffs!$A$2:$AC$20,29,0),"") |
M4,M18,M16,M14,M12,M10,M8,M6 | M4 | =COUNTIF(G4,">"&G5)+COUNTIF(H4,">"&H5)+COUNTIF(I4,">"&I5)+COUNTIF(J4,">"&J5)+COUNTIF(K4,">"&K5)+COUNTIF(L4,">"&L5) |
N4,N18,N16,N14,N12,N10,N8,N6 | N4 | =IF(M4>M5,F4,IF(M4=M5,"Tie","")) |
O4:O19 | O4 | =IFERROR(VLOOKUP($N4,Playoffs!$A$2:$G$21,7,0),"") |
P4:P19 | P4 | =IFERROR(IF($E4<>0,IF(((E4*-1)<L4),"Yes",""),""),"") |
Q4:Q19 | Q4 | =IFERROR(L4-(E4*-1),"") |
M5,M19,M17,M15,M13,M11,M9,M7 | M5 | =COUNTIF(G5,">"&G4)+COUNTIF(H5,">"&H4)+COUNTIF(I5,">"&I4)+COUNTIF(J5,">"&J4)+COUNTIF(K5,">"&K4)+COUNTIF(L5,">"&L4) |
N5,N19,N17,N15,N13,N11,N9,N7 | N5 | =IF(M5>M4,F5,IF(M4=M5,"Tie","")) |
W4,W12,W10,W8,W6 | W4 | =U4/(U4+U5) |
Z4:Z11 | Z4 | =IF(AND(Series="East",Game=1,Team=$Y4),SPRD,"") |
AA4:AA11 | AA4 | =IF(AND(Series="East",Game=1,Team=$Y4),WL,"") |
AB4:AB11 | AB4 | =IF(AND(Series="East",Game=2,Team=$Y4),SPRD,"") |
AC4:AC11 | AC4 | =IF(AND(Series="East",Game=2,Team=$Y4),WL,"") |
AD4:AD11 | AD4 | =IF(AND(Series="East",Game=3,Team=$Y4),SPRD,"") |
AE4:AE11 | AE4 | =IF(AND(Series="East",Game=3,Team=$Y4),WL,"") |
AF4:AF11 | AF4 | =IF(AND(Series="East",Game=4,Team=$Y4),SPRD,"") |
AG4:AG11 | AG4 | =IF(AND(Series="East",Game=4,Team=$Y4),WL,"") |
AH4:AH11 | AH4 | =IF(AND(Series="East",Game=5,Team=$Y4),SPRD,"") |
AI4:AI11 | AI4 | =IF(AND(Series="East",Game=5,Team=$Y4),WL,"") |
AJ4:AJ11 | AJ4 | =IF(AND(Series="East",Game=6,Team=$Y4),SPRD,"") |
AK4:AK11 | AK4 | =IF(AND(Series="East",Game=6,Team=$Y4),WL,"") |
AL4:AL11 | AL4 | =IF(AND(Series="East",Game=7,Team=$Y4),SPRD,"") |
AM4:AM11 | AM4 | =IF(AND(Series="East",Game=7,Team=$Y4),WL,"") |
AN14:AN21,AN4:AN11 | AN4 | =IFERROR(AVERAGE(Z4,AB4,AD4,AF4,AH4,AJ4,AL4),"") |
AO14:AO21,AO4:AO11 | AO4 | =COUNTIF(Z4:AM4,"W") |
AP14:AP21,AP4:AP11 | AP4 | =COUNTIF(Z4:AM4,"L") |
AQ14:AQ21,AQ4:AQ11 | AQ4 | =IFERROR(AO4/(AO4+AP4),"") |
AR14:AR21,AR4:AR11 | AR4 | =Y4 |
T4 | T4 | =COUNTIFS($M$4:$M$26,"6",$D$4:$D$26,"W") |
T5 | T5 | =COUNTIFS($M$4:$M$26,"6",$D$4:$D$26,"L") |
T6 | T6 | =COUNTIFS($M$4:$M$26,"5",$D$4:$D$26,"W") |
T7 | T7 | =COUNTIFS($M$4:$M$26,"5",$D$4:$D$26,"L") |
T8 | T8 | =COUNTIFS($M$4:$M$26,"4",$D$4:$D$26,"W") |
T9 | T9 | =COUNTIFS($M$4:$M$26,"4",$D$4:$D$26,"L") |
T10 | T10 | =COUNTIFS($M$4:$M$26,"3",$D$4:$D$26,"W") |
T11 | T11 | =COUNTIFS($M$4:$M$26,"3",$D$4:$D$26,"L") |
T12 | T12 | =COUNTIFS($M$4:$M$26,"2",$D$4:$D$26,"W") |
T13 | T13 | =COUNTIFS($M$4:$M$26,"2",$D$4:$D$26,"L") |
V4:V13 | V4 | =T4+U4 |
Z14:Z21 | Z14 | =IF(AND(Series="West",Game=1,Team=$Y14),SPRD,"") |
AA14:AA21 | AA14 | =IF(AND(Series="West",Game=1,Team=$Y14),WL,"") |
AB14:AB21 | AB14 | =IF(AND(Series="West",Game=2,Team=$Y14),SPRD,"") |
AC14:AC21 | AC14 | =IF(AND(Series="West",Game=2,Team=$Y14),WL,"") |
AD14:AD21 | AD14 | =IF(AND(Series="West",Game=3,Team=$Y14),SPRD,"") |
AE14:AE21 | AE14 | =IF(AND(Series="West",Game=3,Team=$Y14),WL,"") |
AF14:AF21 | AF14 | =IF(AND(Series="West",Game=4,Team=$Y14),SPRD,"") |
AG14:AG21 | AG14 | =IF(AND(Series="West",Game=4,Team=$Y14),WL,"") |
AH14:AH21 | AH14 | =IF(AND(Series="West",Game=5,Team=$Y14),SPRD,"") |
AI14:AI21 | AI14 | =IF(AND(Series="West",Game=5,Team=$Y14),WL,"") |
AJ14:AJ21 | AJ14 | =IF(AND(Series="West",Game=6,Team=$Y14),SPRD,"") |
AK14:AK21 | AK14 | =IF(AND(Series="West",Game=6,Team=$Y14),WL,"") |
AL14:AL21 | AL14 | =IF(AND(Series="West",Game=7,Team=$Y14),SPRD,"") |
AM14:AM21 | AM14 | =IF(AND(Series="West",Game=7,Team=$Y14),WL,"") |
E4:E19 | E4 | =IFERROR(VLOOKUP($F4,CHOOSE({1,2,3},Favs!$G$5:$G$28,Favs!$D$5:$D$28,,Favs!$H$5:$H$28),2,0),"") |
S20 | S20 | =XOR(Game=2,Series="West",Team=$Y14) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
Game | ='Playoff Favs'!$C$4:$C$19 | Z4:AM4 |
Series | ='Playoff Favs'!$B$4:$B$19 | Z4:AM4 |
SPRD | ='Playoff Favs'!$E$4:$E$19 | AB14:AB21, AD14:AD21, AF14:AF21, AH14:AH21, AJ14:AJ21, AL14:AL21, Z4:Z11, AB4:AB11, AD4:AD11, AF4:AF11, AH4:AH11, AJ4:AJ11, AL4:AL11, Z14:Z21, P4:Q4 |
Team | ='Playoff Favs'!$F$4:$F$19 | Z4:AM4, N4, G4:L4, E4, A4:C4 |
WL | ='Playoff Favs'!$D$4:$D$19 | T4:T13, AC14:AC21, AE14:AE21, AG14:AG21, AI14:AI21, AK14:AK21, AM14:AM21, AA4:AA11, AC4:AC11, AE4:AE11, AG4:AG11, AI4:AI11, AK4:AK11, AM4:AM11, AA14:AA21 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
Q4:Q21 | Expression | ="IF($M4=""Yes"")" | text | NO |
P4:P21 | Cell Value | ="Yes" | text | NO |
D4:E21 | Cell Value | ="L" | text | NO |
D4:E21 | Cell Value | ="W" | text | NO |
L4:L19 | Expression | =L4=MAX(OFFSET(L$4,FLOOR(ROW(L4)-ROW(L$4),2),0,2,1)) | text | NO |
G4:K19 | Expression | =G4=MAX(OFFSET(G$4,FLOOR(ROW(G4)-ROW(G$4),2),0,2,1)) | text | NO |
M4:M19 | Cell Value | top 1 bottom values | text | NO |
M4:M19 | Expression | =M4=MAX(OFFSET(M$4,FLOOR(ROW(M4)-ROW(M$4),2),0,2,1)) | text | NO |