Formula works in one section but not in another???

gtd526

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
696
Office Version
  1. 2019
Platform
  1. 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.

NBA.xlsm
YZAA
2Eastern
3TeamSPRDGM1
4MIL-1.5W
5ORL-2L
6IND-3.5W
7MIA-4L
8BOS-5W
9PHI-6L
10TOR  
11BRK  
12Western
13TeamSPRDGM1
14LAL  
15POR  
16HOU  
17OKC  
18DEN  
19UTA  
20LAC#VALUE!#VALUE!
21DAL#VALUE!#VALUE!
Playoff Favs
Cell Formulas
RangeFormula
Z4:Z11Z4=IF(AND(Series="East",Game=1,Team=$Y4),SPRD,"")
AA4:AA11AA4=IF(AND(Series="East",Game=1,Team=$Y4),WL,"")
Z14:Z21Z14=IF(AND(Series="West",Game=1,Team=$Y14),SPRD,"")
AA14:AA21AA14=IF(AND(Series="West",Game=1,Team=$Y14),WL,"")
Named Ranges
NameRefers ToCells
Game='Playoff Favs'!$C$4:$C$19Z4:AA4
Series='Playoff Favs'!$B$4:$B$19Z4:AA4
SPRD='Playoff Favs'!$E$4:$E$19Z4:Z11, Z14:Z21
Team='Playoff Favs'!$F$4:$F$19Z4:AA4
WL='Playoff Favs'!$D$4:$D$19AA4:AA11, AA14:AA21


Where Im pulling the data from:
NBA.xlsm
ABCDEF
1Copy Teams From "Fav's"
2reversereversereverseATSreverse
3SectionSeriesGame*W/L*SPRDTEAM
4SemiEast1W-1.5MIL
5SemiEast1L-2ORL
6SemiEast1W-3.5IND
7SemiEast1L-4MIA
8SemiEast1W-5BOS
9SemiEast1L-6PHI
10SemiEast2W-3.5TOR
11SemiEast2L-2BRK
12SemiWest1W-1.5LAL
13SemiWest1L-2POR
14SemiWest2W-3.5HOU
15SemiWest2L-4OKC
16SemiWest1W-5DEN
17SemiWest1L-6UTA
18SemiWest2W-3.5LAC
19SemiWest2L-2DAL
Playoff Favs
Cell Formulas
RangeFormula
A4:A19A4=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:B19B4=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:C19C4=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:E19E4=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
NameRefers ToCells
Team='Playoff Favs'!$F$4:$F$19E4, A4:C4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D4:E21Cell Value="L"textNO
D4:E21Cell Value="W"textNO

Below is ALL.
NBA.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
1Copy Teams From "Fav's"+/-
2reversereversereverseATSreverseFav'sIs +/-ATSEastern
3SectionSeriesGame*W/L*SPRDTEAMW%PTSFG%3P%REB+/-> ThanMy FavW %> ATS ?DiffFav'svs SPRDPreviousTotalW%TeamSPRDGM1SPRDGM2SPRDGM3SPRDGM4SPRDGM5SPRDGM6SPRDGM7Avg SPRDWLW%Team
4SemiEast1W-1.5MIL75%11648.939.14986MIL75%Yes6.56W:22467%MIL-1.5W            -1.51 100%MIL
5SemiEast1L-2ORL25%10842.738.042-8    -10.0L:213ORL-2L            -2 1 ORL
6SemiEast1W-3.5IND 10145.537.537-11    -14.05W:213100%IND-3.5W            -3.51 100%IND
7SemiEast1L-4MIA100%11146.439.146116MIA100%Yes6.5L:  MIA-4L            -4 1 MIA
8SemiEast1W-5BOS100%11245.235.043125BOS100%Yes6.84W:  #DIV/0!BOS-5W            -51 100%BOS
9SemiEast1L-6PHI 10139.626.446-121   -17.8L:  PHI-6L            -6 1 PHI
10SemiEast2W-3.5TOR100%12649.543.351216TOR100%Yes17.03W:11 TOR  -3.5W          -3.51 100%TOR
11SemiEast2L-2BRK 10639.733.744-21    -22.5L:112BRK  -2L          -2 1 BRK
12SemiWest1W-1.5LAL75%113.846.933.150115LAL75%Yes9.52W:112100%Western
13SemiWest1L-2POR25%102.841.435.743-111   -13.0L:11TeamSPRDGM1SPRDGM2SPRDGM3SPRDGM4SPRDGM5SPRDGM6SPRDGM7Avg SPRDWLW%Team
14SemiWest2W-3.5HOU50%113.844.135.64133HOU50% -0.2LAL                  LAL
15SemiWest2L-4OKC50%110.545.535.049-32   -7.3Copy/Paste SVPOR                  POR
16SemiWest1W-5DEN25%113.546.242.742-12    -17.0To: Oddsafter ea gameHOU                  HOU
17SemiWest1L-6UTA75%125.551.643.344126UTA75%Yes6.0to preserve recordOKC                  OKC
18SemiWest2W-3.5LAC50%123.847.535.44402   -3.2To: FavsDEN                  DEN
19SemiWest2L-2DAL50%123.549.238.44703DAL50% -2.3IFS(test1, value-if-true, test2, value-if-true)UTA                  UTA
20#VALUE!LAC#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!    LAC
21IF(OR(A5="Red",B5="Green"),TRUE,FALSE)DAL#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!    DAL
Playoff Favs
Cell Formulas
RangeFormula
A4:A19A4=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:B19B4=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:C19C4=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:G19G4=IFERROR(VLOOKUP($F4,Playoffs!$A$2:$G$20,7,0),"")
H4:H19H4=IFERROR(VLOOKUP($F4,Playoffs!$A$2:$I$20,9,0),"")
I4:I19I4=IFERROR(VLOOKUP($F4,Playoffs!$A$2:$L$20,12,0),"")
J4:J19J4=IFERROR(VLOOKUP($F4,Playoffs!$A$2:$O$20,15,0),"")
K4:K19K4=IFERROR(VLOOKUP($F4,Playoffs!$A$2:$U$20,21,0),"")
L4:L19L4=IFERROR(VLOOKUP($F4,Playoffs!$A$2:$AC$20,29,0),"")
M4,M18,M16,M14,M12,M10,M8,M6M4=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,N6N4=IF(M4>M5,F4,IF(M4=M5,"Tie",""))
O4:O19O4=IFERROR(VLOOKUP($N4,Playoffs!$A$2:$G$21,7,0),"")
P4:P19P4=IFERROR(IF($E4<>0,IF(((E4*-1)<L4),"Yes",""),""),"")
Q4:Q19Q4=IFERROR(L4-(E4*-1),"")
M5,M19,M17,M15,M13,M11,M9,M7M5=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,N7N5=IF(M5>M4,F5,IF(M4=M5,"Tie",""))
W4,W12,W10,W8,W6W4=U4/(U4+U5)
Z4:Z11Z4=IF(AND(Series="East",Game=1,Team=$Y4),SPRD,"")
AA4:AA11AA4=IF(AND(Series="East",Game=1,Team=$Y4),WL,"")
AB4:AB11AB4=IF(AND(Series="East",Game=2,Team=$Y4),SPRD,"")
AC4:AC11AC4=IF(AND(Series="East",Game=2,Team=$Y4),WL,"")
AD4:AD11AD4=IF(AND(Series="East",Game=3,Team=$Y4),SPRD,"")
AE4:AE11AE4=IF(AND(Series="East",Game=3,Team=$Y4),WL,"")
AF4:AF11AF4=IF(AND(Series="East",Game=4,Team=$Y4),SPRD,"")
AG4:AG11AG4=IF(AND(Series="East",Game=4,Team=$Y4),WL,"")
AH4:AH11AH4=IF(AND(Series="East",Game=5,Team=$Y4),SPRD,"")
AI4:AI11AI4=IF(AND(Series="East",Game=5,Team=$Y4),WL,"")
AJ4:AJ11AJ4=IF(AND(Series="East",Game=6,Team=$Y4),SPRD,"")
AK4:AK11AK4=IF(AND(Series="East",Game=6,Team=$Y4),WL,"")
AL4:AL11AL4=IF(AND(Series="East",Game=7,Team=$Y4),SPRD,"")
AM4:AM11AM4=IF(AND(Series="East",Game=7,Team=$Y4),WL,"")
AN14:AN21,AN4:AN11AN4=IFERROR(AVERAGE(Z4,AB4,AD4,AF4,AH4,AJ4,AL4),"")
AO14:AO21,AO4:AO11AO4=COUNTIF(Z4:AM4,"W")
AP14:AP21,AP4:AP11AP4=COUNTIF(Z4:AM4,"L")
AQ14:AQ21,AQ4:AQ11AQ4=IFERROR(AO4/(AO4+AP4),"")
AR14:AR21,AR4:AR11AR4=Y4
T4T4=COUNTIFS($M$4:$M$26,"6",$D$4:$D$26,"W")
T5T5=COUNTIFS($M$4:$M$26,"6",$D$4:$D$26,"L")
T6T6=COUNTIFS($M$4:$M$26,"5",$D$4:$D$26,"W")
T7T7=COUNTIFS($M$4:$M$26,"5",$D$4:$D$26,"L")
T8T8=COUNTIFS($M$4:$M$26,"4",$D$4:$D$26,"W")
T9T9=COUNTIFS($M$4:$M$26,"4",$D$4:$D$26,"L")
T10T10=COUNTIFS($M$4:$M$26,"3",$D$4:$D$26,"W")
T11T11=COUNTIFS($M$4:$M$26,"3",$D$4:$D$26,"L")
T12T12=COUNTIFS($M$4:$M$26,"2",$D$4:$D$26,"W")
T13T13=COUNTIFS($M$4:$M$26,"2",$D$4:$D$26,"L")
V4:V13V4=T4+U4
Z14:Z21Z14=IF(AND(Series="West",Game=1,Team=$Y14),SPRD,"")
AA14:AA21AA14=IF(AND(Series="West",Game=1,Team=$Y14),WL,"")
AB14:AB21AB14=IF(AND(Series="West",Game=2,Team=$Y14),SPRD,"")
AC14:AC21AC14=IF(AND(Series="West",Game=2,Team=$Y14),WL,"")
AD14:AD21AD14=IF(AND(Series="West",Game=3,Team=$Y14),SPRD,"")
AE14:AE21AE14=IF(AND(Series="West",Game=3,Team=$Y14),WL,"")
AF14:AF21AF14=IF(AND(Series="West",Game=4,Team=$Y14),SPRD,"")
AG14:AG21AG14=IF(AND(Series="West",Game=4,Team=$Y14),WL,"")
AH14:AH21AH14=IF(AND(Series="West",Game=5,Team=$Y14),SPRD,"")
AI14:AI21AI14=IF(AND(Series="West",Game=5,Team=$Y14),WL,"")
AJ14:AJ21AJ14=IF(AND(Series="West",Game=6,Team=$Y14),SPRD,"")
AK14:AK21AK14=IF(AND(Series="West",Game=6,Team=$Y14),WL,"")
AL14:AL21AL14=IF(AND(Series="West",Game=7,Team=$Y14),SPRD,"")
AM14:AM21AM14=IF(AND(Series="West",Game=7,Team=$Y14),WL,"")
E4:E19E4=IFERROR(VLOOKUP($F4,CHOOSE({1,2,3},Favs!$G$5:$G$28,Favs!$D$5:$D$28,,Favs!$H$5:$H$28),2,0),"")
S20S20=XOR(Game=2,Series="West",Team=$Y14)
Named Ranges
NameRefers ToCells
Game='Playoff Favs'!$C$4:$C$19Z4:AM4
Series='Playoff Favs'!$B$4:$B$19Z4:AM4
SPRD='Playoff Favs'!$E$4:$E$19AB14: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$19Z4:AM4, N4, G4:L4, E4, A4:C4
WL='Playoff Favs'!$D$4:$D$19T4: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
CellConditionCell FormatStop If True
Q4:Q21Expression="IF($M4=""Yes"")"textNO
P4:P21Cell Value="Yes"textNO
D4:E21Cell Value="L"textNO
D4:E21Cell Value="W"textNO
L4:L19Expression=L4=MAX(OFFSET(L$4,FLOOR(ROW(L4)-ROW(L$4),2),0,2,1))textNO
G4:K19Expression=G4=MAX(OFFSET(G$4,FLOOR(ROW(G4)-ROW(G$4),2),0,2,1))textNO
M4:M19Cell Valuetop 1 bottom valuestextNO
M4:M19Expression=M4=MAX(OFFSET(M$4,FLOOR(ROW(M4)-ROW(M$4),2),0,2,1))textNO
 
Sort by date Sort by votes
gtd526 said:
Thank you for the reply.
GM1 is correct.
SPRD is incorrect. It seems the be shifting down by 2 rows for the response.
NBA.xlsm
YZAA
12Western
13TeamSPRDGM1
14LAL-3.5W
15POR-4L
16HOU  
17OKC  
18DEN-3.5W
19UTA-2L
20LAC  
21DAL  
Playoff Favs
Cell Formulas
RangeFormula
Z14:Z21Z14=IF(COUNTIFS(Series,"West",Game,1,Team,$Y14)>0,SPRD,"")
AA14:AA21AA14=IF(COUNTIFS(Series,"West",Game,1,Team,$Y14)>0,WL,"")
Named Ranges
NameRefers ToCells
Game='Playoff Favs'!$C$4:$C$19Z14:AA21
Series='Playoff Favs'!$B$4:$B$19Z14:AA21
SPRD='Playoff Favs'!$E$4:$E$19Z14:Z21
Team='Playoff Favs'!$F$4:$F$19Z14:AA21
WL='Playoff Favs'!$D$4:$D$19AA14:AA21


NBA.xlsm
EF
3SPRDTEAM
4-1.5MIL
5-2ORL
6-3.5IND
7-4MIA
8-5BOS
9-6PHI
10-3.5TOR
11-2BRK
12-1.5LAL
13-2POR
14-3.5HOU
15-4OKC
16-5DEN
17-6UTA
18-3.5LAC
19-2DAL
Playoff Favs
Cell Formulas
RangeFormula
E4:E19E4=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
NameRefers ToCells
Team='Playoff Favs'!$F$4:$F$19E4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D4:E21Cell Value="L"textNO
D4:E21Cell Value="W"textNO


Thank you for the reply.
Works very well.
thx
NBA.xlsm
ABACADAEAFAG
13SPRDGM2SPRDGM3SPRDGM4
14      
15      
16-3.5L    
17-4L    
18  -5W  
19  -6W  
20    -3.5L
21    -2L
Playoff Favs
Cell Formulas
RangeFormula
AB14:AB21AB14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=2)/(Team=$Y14),SPRD),"")
AC14:AC21AC14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=2)/(Team=$Y14),WL),"")
AD14:AD21AD14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=3)/(Team=$Y14),SPRD),"")
AE14:AE21AE14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=3)/(Team=$Y14),WL),"")
AF14:AF21AF14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=4)/(Team=$Y14),SPRD),"")
AG14:AG21AG14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=4)/(Team=$Y14),WL),"")
Named Ranges
NameRefers ToCells
Game='Playoff Favs'!$C$4:$C$19AB14:AG21
Series='Playoff Favs'!$B$4:$B$19AB14:AG21
SPRD='Playoff Favs'!$E$4:$E$19AB14:AB21, AD14:AD21, AF14:AF21
Team='Playoff Favs'!$F$4:$F$19AB14:AG21
WL='Playoff Favs'!$D$4:$D$19AC14:AC21, AE14:AE21, AG14:AG21


Thanks for the reply. Working!! thx.
NBA.xlsm
ABACADAEAFAG
13SPRDGM2SPRDGM3SPRDGM4
14      
15      
16-3.5L    
17-4L    
18  -5W  
19  -6W  
20    -3.5L
21    -2L
Playoff Favs
Cell Formulas
RangeFormula
AB14:AB21AB14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=2)/(Team=$Y14),SPRD),"")
AC14:AC21AC14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=2)/(Team=$Y14),WL),"")
AD14:AD21AD14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=3)/(Team=$Y14),SPRD),"")
AE14:AE21AE14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=3)/(Team=$Y14),WL),"")
AF14:AF21AF14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=4)/(Team=$Y14),SPRD),"")
AG14:AG21AG14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=4)/(Team=$Y14),WL),"")
Named Ranges
NameRefers ToCells
Game='Playoff Favs'!$C$4:$C$19AB14:AG21
Series='Playoff Favs'!$B$4:$B$19AB14:AG21
SPRD='Playoff Favs'!$E$4:$E$19AB14:AB21, AD14:AD21, AF14:AF21
Team='Playoff Favs'!$F$4:$F$19AB14:AG21
WL='Playoff Favs'!$D$4:$D$19AC14:AC21, AE14:AE21, AG14:AG21
Click to expand...
I wanted to add one more criteria, Section="Semi". Is this how it would look?
=IFERROR(LOOKUP(2,1/(Section="Semi")/(Series="East")/(Game=1)/(Team=$Y26),SPRD),"")
Fluff said:
You already have a working formulae :)
Click to expand...
No problem. Curiosity killed the cat :(
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
You must log in or register to reply here.

Similar threads

gtd526
  • Solved
Count consecutive value
Replies
5
Views
377
gtd526
gtd526
gtd526
  • Question Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
331
gtd526
gtd526
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
368
gtd526
gtd526
gtd526
  • Question Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
376
gtd526
gtd526
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
359
Tetra201
Tetra201

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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