gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 675
- Office Version
- 2019
- Platform
- Windows
Hello,
It seems the formula is reading 'blanks' giving a result in cells CC7:CF9 when it should be blank, because there are no results (CB7:CB9).
How can I ignore blanks in this formula (CC5:CF9).
Also, when I hit save, the list will reverse (CB5:CB9), everything is correct, but the list reverses. How can I stop that?
Thank You.
It seems the formula is reading 'blanks' giving a result in cells CC7:CF9 when it should be blank, because there are no results (CB7:CB9).
How can I ignore blanks in this formula (CC5:CF9).
Also, when I hit save, the list will reverse (CB5:CB9), everything is correct, but the list reverses. How can I stop that?
Thank You.
NFL.xlsm | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | CB | CC | CD | CE | CF | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
4 | DAL | 5 | 5.0 | TEAM | COVER% | ATS | ATS+/- | MOV | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
5 | TB | -5 | -5.0 | W | AA:AN (ALL) | TB | -5 | BUF | 63% | -3 | 4.6 | 6.8 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
6 | PHI | -10 | -10.0 | TB | 60% | -5 | 5.2 | 9.1 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
7 | ATL | 10 | 10.0 | AA:AN (ALL) | 31% | 5 | -3 | -4.9 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
8 | PIT | 3 | 3.0 | 31% | 5 | -3 | -4.9 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
9 | BUF | -3 | -3.0 | AK:AN (PTS) | BUF | -3 | 31% | 5 | -3 | -4.9 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Weekly Picks |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H4,H8,H6 | H4 | =IFERROR(IF(AND($A4=$AY4,IFERROR($BA4>=0.6,TRUE),IFERROR($BC4>=0.6,TRUE),IFERROR($BB4>=0.525,TRUE),IFERROR($BD4>=0.525,TRUE),$AU4=$AY$4),$A4,""),"") |
I4:I9 | I4 | =IF(AND($F4="",$H4<>""),$B4,"") |
CB5:CB9 | CB5 | =IFERROR(LOOKUP(2, 1/((COUNTIF($CB$5:CB5, $H$4:$H$35)=0)*($H$4:$H$35<>"")),$H$4:$H$35),"") |
CC5 | CC5 | =IFERROR(INDEX(V4:V35,MATCH(CB5,H4:H35,0)),"") |
CD5:CD9 | CD5 | =IFERROR(INDEX($B$4:$B$35,MATCH($CB5,$H$4:$H$35,0)),"") |
CE5:CE9 | CE5 | =IFERROR(INDEX($Y$4:$Y$35,MATCH($CB5,$H$4:$H$35,0)),"") |
CF5:CF9 | CF5 | =IFERROR(INDEX($W$4:$W$35,MATCH($CB5,$H$4:$H$35,0)),"") |
CC6:CC9 | CC6 | =IFERROR(INDEX($V$4:$V$35,MATCH($CB6,$H$4:$H$35,0)),"") |
H7 | H7 | =IFERROR(IF(AND($A7=$AY7,IFERROR($BA7>=0.6,TRUE),IFERROR($BC7>=0.6,TRUE),IFERROR($BB7>=0.525,TRUE),IFERROR($BD7>=0.525,TRUE),$AU7=$AY$5),$A7,""),"") |
C4:C9 | C4 | =IFERROR(VLOOKUP(A4,'My ATS Avg'!$A$3:$AD$34,27,0),"") |
G4,G8,G6 | G4 | =IF(AND($A4=$AY4,$AA4>$AA5,$AB4>$AB5,$AC4>$AC5,$AD4>$AD5,$AE4>$AE5,$AF4>$AF5,$AG4<$AG5,$AH4>$AH5,$AI4>$AI5,$AJ4>$AJ5,$AK4>$AK5,$AL4>$AL5,$AM4>$AM5,$AN4>$AN5),"AA:AN (ALL)",IF(AND($A4=$AY4,$AK4>$AK5,$AL4>$AL5,$AM4>$AM5,$AN4>$AN5),"AK:AN (PTS)",IF(AND($A4=$AY4,$AD4>$AD5,$AE4>$AE5,$AF4>$AF5,$AG4<$AG5,$AH4>$AH5,$AI4>$AI5),"AD:AI (Passing)",IF(AND($A4=$AY4,$R4>$R5,$T4<$T5,$V4>$V5,$W4>$W5,$Y4>$Y5),"R,T,V,W,Y (ATS)",IF(AND($A4=$AY4,$AO4>$AO5,$AF4>$AF5,$AG4<$AG5,$AH4>$AH5,$AK4>$AK5,$AM4>$AM5),"AF:AH,AK,AM,AO",IF(AND($A4=$AY4,$AA4>$AA5,$AB4>$AB5,$AC4>$AC5),"AA:AC (SRS)",IF(AND($A4=$AY4,$AY4=$L4,$AY4=$O4,$W4>$B4,$Y4>$B4,$AU4=$AY4),"6 Matches",IF(AND($A4=$AY4,$AY4=$L4,$W4>$B4,$Y4>$B4,$AU4=$AY4),"5 Matches",IF(AND($A4=$AY4,$W4>$B4,$Y4>$B4,$AU4=$AY4),"4 Matches",IF(AND($A4=$AY4,$AY4=$L4,$A44=$O4),"3 Matches",IF(AND($A4=$AY4,$S4<$S5,$U4<$U5),"S,U",IF(AND($A4=$AY4,$V4>$V5,$W4>$W5,$Z4>$Z5),"V-W,Z","")))))))))))) |
G5,G9,G7 | G5 | =IF(AND($A5=$AY5,$AA5>$AA4,$AB5>$AB4,$AC5>$AC4,$AD5>$AD4,$AE5>$AE4,$AF5>$AF4,$AG5<$AG4,$AH5>$AH4,$AI5>$AI4,$AJ5>$AJ4,$AK5>$AK4,$AL5>$AL4,$AM5>$AM4,$AN5>$AN4),"AA:AN (ALL)",IF(AND($A5=$AY5,$AK5>$AK4,$AL5>$AL4,$AM5>$AM4,$AN5>$AN4),"AK:AN (PTS)",IF(AND($A5=$AY5,$AD5>$AD4,$AE5>$AE4,$AF5>$AF4,$AG5<$AG4,$AH5>$AH4,$AI5>$AI4),"AD:AI (Passing)",IF(AND($A5=$AY5,$R5>$R4,$T5<$T4,$V5>$V4,$W5>$W4,$Y5>$Y4),"R,T,V,W,Y (ATS)",IF(AND($A5=$AY5,$AO5>$AO4,$AF5>$AF4,$AG5<$AG4,$AH5>$AH4,$AK5>$AK4,$AM5>$AM4),"AF:AH,AK,AM,AO",IF(AND($A5=$AY5,$AA5>$AA4,$AB5>$AB4,$AC5>$AC4),"AA:AC (SRS)",IF(AND($A5=$AY5,$AY5=$L5,$L5=$O5,$X5<>"",$X5>0,$Z5<>"",$Z5>0,$AU5=$AY5),"6 Matches",IF(AND($A5=$AY5,$AY5=$L5,$W5>$B5,$Y5>$B5,$AU5=$AY5),"5 Matches",IF(AND($A5=$AY5,$W5>$B5,$Y5>$B5,$AU5=$AY5),"4 Matches",IF(AND($A5=$AY5,$AY5=$L5,$AY5=$O5),"3 Matches",IF(AND($A5=$AY5,$S5<$S4,$U5<$U4),"S,U",IF(AND($A5=$AY5,$V5>$V4,$W5>$W4,$Z5>$Z4),"V-W,Z","")))))))))))) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
D4:E35 | Cell Value | ="psh" | text | NO |
CC5:CC11 | Cell Value | top 1 values | text | NO |
D4:E35 | Cell Value | ="L" | text | NO |
D4:E35 | Cell Value | ="W" | text | NO |
B4:B35 | Expression | =LEN($H4)>1 | text | NO |
B4:C35 | Cell Value | <0 | text | NO |
H4:I35 | Cell | does not contain a blank value | text | NO |
A4:A35,Z4:Z35,AU4:AU35,AY4:AY35,L4:L35,O4:O35,X4:X35 | Expression | =AND($A4=$L4,$L4=$O4,$O4=$AY4,$X4<>"",$X4>0,$Z4<>"",$Z4>0,$AU4=$AY4) | text | NO |
A4:A35,Z4:Z35,AU4:AU35,AY4:AY35,L4:L35,X4:X35 | Expression | =AND($A4=$L4,$X4<>"",$X4>0,$Z4<>"",$Z4>0,$AU4=$AY4) | text | NO |
A4:A35,Z4:Z35,AU4:AU35,AY4:AY35,X4:X35 | Expression | =AND($A4=$AY4,$X4<>"",$X4>0,$Z4<>"",$Z4>0,$AU4=$AY4) | text | NO |
A4:A35,L4:L35,O4:O35,AY4:AY35 | Expression | =AND($A4=$AY4,$A4=$L4,$L4=$O4) | text | NO |