Ignore blanks in formula

gtd526

gtd526

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


NFL.xlsm
ABCDEFGHICBCCCDCECF
4DAL55.0   TEAMCOVER%ATSATS+/-MOV
5TB-5-5.0WAA:AN (ALL)TB-5BUF63%-34.66.8
6PHI-10-10.0   TB60%-55.29.1
7ATL1010.0AA:AN (ALL)   31%5-3-4.9
8PIT33.0    31%5-3-4.9
9BUF-3-3.0AK:AN (PTS)BUF-3 31%5-3-4.9
Weekly Picks
Cell Formulas
RangeFormula
H4,H8,H6H4=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:I9I4=IF(AND($F4="",$H4<>""),$B4,"")
CB5:CB9CB5=IFERROR(LOOKUP(2, 1/((COUNTIF($CB$5:CB5, $H$4:$H$35)=0)*($H$4:$H$35<>"")),$H$4:$H$35),"")
CC5CC5=IFERROR(INDEX(V4:V35,MATCH(CB5,H4:H35,0)),"")
CD5:CD9CD5=IFERROR(INDEX($B$4:$B$35,MATCH($CB5,$H$4:$H$35,0)),"")
CE5:CE9CE5=IFERROR(INDEX($Y$4:$Y$35,MATCH($CB5,$H$4:$H$35,0)),"")
CF5:CF9CF5=IFERROR(INDEX($W$4:$W$35,MATCH($CB5,$H$4:$H$35,0)),"")
CC6:CC9CC6=IFERROR(INDEX($V$4:$V$35,MATCH($CB6,$H$4:$H$35,0)),"")
H7H7=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:C9C4=IFERROR(VLOOKUP(A4,'My ATS Avg'!$A$3:$AD$34,27,0),"")
G4,G8,G6G4=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,G7G5=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
CellConditionCell FormatStop If True
D4:E35Cell Value="psh"textNO
CC5:CC11Cell Valuetop 1 valuestextNO
D4:E35Cell Value="L"textNO
D4:E35Cell Value="W"textNO
B4:B35Expression=LEN($H4)>1textNO
B4:C35Cell Value<0textNO
H4:I35Celldoes not contain a blank value textNO
A4:A35,Z4:Z35,AU4:AU35,AY4:AY35,L4:L35,O4:O35,X4:X35Expression=AND($A4=$L4,$L4=$O4,$O4=$AY4,$X4<>"",$X4>0,$Z4<>"",$Z4>0,$AU4=$AY4)textNO
A4:A35,Z4:Z35,AU4:AU35,AY4:AY35,L4:L35,X4:X35Expression=AND($A4=$L4,$X4<>"",$X4>0,$Z4<>"",$Z4>0,$AU4=$AY4)textNO
A4:A35,Z4:Z35,AU4:AU35,AY4:AY35,X4:X35Expression=AND($A4=$AY4,$X4<>"",$X4>0,$Z4<>"",$Z4>0,$AU4=$AY4)textNO
A4:A35,L4:L35,O4:O35,AY4:AY35Expression=AND($A4=$AY4,$A4=$L4,$L4=$O4)textNO
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
gtd526 said:
How can I ignore blanks in this formula (CC5:CF9).
Click to expand...
That can be solved by adding a simple IF to the formula,
Excel Formula: 
=IF(CB5="","",IFERROR(INDEX(V4:V35,MATCH(CB5,H4:H35,0)),""))
gtd526 said:
when I hit save, the list will reverse (CB5:CB9), everything is correct, but the list reverses. How can I stop that?
Click to expand...
Do you have vba in the workbook that is sorting the data in some way?

I can't test your formulas in a workbook as they refer to data that is not part of your example but I can see no other reason for it to happen.
 
Upvote 0
jasonb75 said:
That can be solved by adding a simple IF to the formula,
Excel Formula: 
=IF(CB5="","",IFERROR(INDEX(V4:V35,MATCH(CB5,H4:H35,0)),""))

Do you have vba in the workbook that is sorting the data in some way?

I can't test your formulas in a workbook as they refer to data that is not part of your example but I can see no other reason for it to happen.
Click to expand...
The If statement works. Thank you.
Heres the entire range of cells.
=IFERROR(LOOKUP(2, 1/((COUNTIF($CB$5:CB5, $H$4:$H$35)=0)*($H$4:$H$35<>"")),$H$4:$H$35),"")
I'm not sure if its because its refering to itself in the formula?
I have no VBA that is sorting this data.

NFL.xlsm
ABCDEHCBCCCDCECFCGCH
4DAL55.0 TEAMCOVER%ATSATS+/-MOVATS vs ATS +/-ATS vs MOV
5TB-5-5.0WTBBUF63%-34.66.81.63.8
6PHI-10-10.0 TB60%-55.29.10.24.1
7ATL1010.0        
8PIT33.0        
9BUF-3-3.0BUF       
10NYJ33.0 
11CAR-3-3.0 
12MIN77.0 
13CIN-7-7.0 CURRENT SEASON - WHO WINS MORE? -ATS OR +ATS
14SF1010.0 (-) ATS(+) ATS
15DET-10-10.0 W:0W:0
16JAX1313.0 L:0L:0
17HOU-13-13.0 PSH:0PSH:0
18SEA66.0 PREVIOUS
19IND-6-6.0 (-) ATS(+) ATS
20ARZ77.0 W:2W:6
21TEN-7-7.0 L:4L:3
22LAC1313.0 PSH:0PSH:0
23WAS-13-13.0 TOTAL
24CLE1010.0 (-) ATS(+) ATS
25KC-10-10.0 W:2$9.10W:6$18.20
26MIA-13-13.0 L:4$20.00L:3$10.00
27NE1313.0 PSH:0PSH:0
28GB-3-3.0-$10.90$8.20
29NO33.0 633%967%
30DEN77.0 
31NYG-7-7.0 
32CHI1111.0 CurrentTotalCurrent
33LAR-11-11.0 W:W:650.0%W:
34BAL1212.0 L:L:6L:
35LVR-12-12.0 Psh:Psh:0Psh:
Weekly Picks
Cell Formulas
RangeFormula
H4,H34,H32,H30,H26,H24,H22,H20,H18,H16,H14,H12,H10,H8,H6H4=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,""),"")
CB5:CB9CB5=IFERROR(LOOKUP(2, 1/((COUNTIF($CB$5:CB5, $H$4:$H$35)=0)*($H$4:$H$35<>"")),$H$4:$H$35),"")
CC5:CC9CC5=IF(CB5="","",IFERROR(INDEX(V4:V35,MATCH(CB5,H4:H35,0)),""))
CD5:CD9CD5=IF(CB5="","",IFERROR(INDEX($B$4:$B$35,MATCH($CB5,$H$4:$H$35,0)),""))
CE5:CE9CE5=IF(CB5="","",IFERROR(INDEX($Y$4:$Y$35,MATCH($CB5,$H$4:$H$35,0)),""))
CF5:CF9CF5=IF(CB5="","",IFERROR(INDEX($W$4:$W$35,MATCH($CB5,$H$4:$H$35,0)),""))
CG5:CG9CG5=IFERROR(IF(CD5>0,"",IF(AND($CD5<0,$CE5<0),ABS($CE5)-ABS($CDB5),IF(AND($CD5<0,$CE5>0),$CE5-ABS($CD5),""))),"")
CH5:CH9CH5=IFERROR(IF(CD5>0,"",IF(AND($CD5<0,$CF5<0),ABS($CF5)-ABS($CDB5),IF(AND($CD5<0,$CF5>0),$CF5-ABS($CD5),""))),"")
H7,H35,H33,H31,H29,H27,H25,H23,H21,H19,H17,H15,H13,H11H7=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,""),"")
CC15CC15=COUNTIFS($B4,"<0",$E4,"W")
CC16CC16=COUNTIFS($B4,"<0",$E4,"L")
CC17CC17=COUNTIFS($B4,"<0",$E4,"psh")
CF15CF15=COUNTIFS($B4,">0",$E4,"W")
CF16CF16=COUNTIFS($B4,">0",$E4,"L")
CF17CF17=COUNTIFS($B4,">0",$E4,"psh")
CF25:CF27,CC25:CC27CC25=CC15+CC20
CC29,CF29CC29=CC25+CC26
C35,C4:C33C4=IFERROR(VLOOKUP(A4,'My ATS Avg'!$A$3:$AD$34,27,0),"")
CF33CF33=IFERROR(CE33/CE36,"")
CE33:CE35CE33=CC37+CC33
Cells with Conditional Formatting
CellConditionCell FormatStop If True
CG5:CH9Cell Value<0textNO
CG5:CH9Cell Value>0textNO
CF27Cell Valuetop 1 bottom valuestextNO
CF27Cell Valuetop 1 valuestextNO
CF33Expression=CC2="14"textNO
CB33:CF33Expression=CD2="3"textNO
CF33Cell Valuetop 1 bottom valuestextNO
CF33Cell Valuetop 1 valuestextNO
CF33Cell Value<"MAX"textNO
CF33Cell Value>"MIN"textNO
CF25:CF26Cell Valuetop 1 bottom valuestextNO
CF25:CF26Cell Valuetop 1 valuestextNO
CC25:CC27Cell Valuetop 1 bottom valuestextNO
CC25:CC27Cell Valuetop 1 valuestextNO
CD28Cell Value<0textNO
CD28Cell Value>0textNO
CF20:CF22Cell Valuetop 1 bottom valuestextNO
CF20:CF22Cell Valuetop 1 valuestextNO
CC20:CC22Cell Valuetop 1 bottom valuestextNO
CC20:CC22Cell Valuetop 1 valuestextNO
D4:E35Cell Value="psh"textNO
CC5:CC11Cell Valuetop 1 valuestextNO
CN28:CN29,CG28:CG29Expression=$CN$28<0textNO
CN28:CN29,CG28:CG29Expression=$CN$29>0textNO
CK28:CK29,CD28:CD29Expression=$CK$28<0textNO
CK28:CK29,CD28:CD29Expression=$CK$28>0textNO
D4:E35Cell Value="L"textNO
D4:E35Cell Value="W"textNO
B4:B35Expression=LEN($H4)>1textNO
B4:C35Cell Value<0textNO
CG31Expression=#REF!="10"textNO
CG31Expression=CC2="10"textNO
CB31Expression=#REF!="14"textNO
CB31Expression=CC2="14"textNO
H4:I35Celldoes not contain a blank value textNO
A4:A35,Z4:Z35,AU4:AU35,AY4:AY35,L4:L35,O4:O35,X4:X35Expression=AND($A4=$L4,$L4=$O4,$O4=$AY4,$X4<>"",$X4>0,$Z4<>"",$Z4>0,$AU4=$AY4)textNO
A4:A35,Z4:Z35,AU4:AU35,AY4:AY35,L4:L35,X4:X35Expression=AND($A4=$L4,$X4<>"",$X4>0,$Z4<>"",$Z4>0,$AU4=$AY4)textNO
A4:A35,Z4:Z35,AU4:AU35,AY4:AY35,X4:X35Expression=AND($A4=$AY4,$X4<>"",$X4>0,$Z4<>"",$Z4>0,$AU4=$AY4)textNO
A4:A35,L4:L35,O4:O35,AY4:AY35Expression=AND($A4=$AY4,$A4=$L4,$L4=$O4)textNO
 
Upvote 0
Is what you're showing us in the correct order or is it reversed? No matter what I do it is always the opposite to what your mini sheet shows, TB in CB5, BUF in CB6.

It's possible that the circular reference is the cause, you could try a different formula without a circular ref to see if that fixes it but there is no obvious cause that stands out anywhere.

Try this in CB5 and fill down, see if it works correctly.
Excel Formula: 
=IFERROR(INDEX(H:H,AGGREGATE(15,6,ROW(H$5:H$9)/(H$5:H$9<>"")/ISNA(MATCH(H$5:H$9,CB$4:CB4,0)),1)),"")

edit:- formula changed, copied the wrong one from the test sheet.
 
Upvote 0
Solution
jasonb75 said:
Is what you're showing us in the correct order or is it reversed? No matter what I do it is always the opposite to what your mini sheet shows, TB in CB5, BUF in CB6.

It's possible that the circular reference is the cause, you could try a different formula without a circular ref to see if that fixes it but there is no obvious cause that stands out anywhere.

Try this in CB5 and fill down, see if it works correctly.
Excel Formula: 
=IFERROR(INDEX(H:H,AGGREGATE(15,6,ROW(H$5:H$9)/(H$5:H$9<>"")/ISNA(MATCH(H$5:H$9,CB$4:CB4,0)),1)),"")

edit:- formula changed, copied the wrong one from the test sheet.
Click to expand...
Works great. It doesn't reverse the order. Thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Question Question
convert cell value to formula
Replies
8
Views
360
Fluff
Fluff
gtd526
  • Question Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
305
gtd526
gtd526
D
  • Solved
Refining my code?
2
Replies
16
Views
482
Joe4
Joe4
gtd526
  • Solved
vba replace the first 3 characters - (NBA indicators)
Replies
1
Views
464
gtd526
gtd526
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
363
gtd526
gtd526

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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