Ignore blanks in formula

gtd526

Active Member
Joined
Jul 30, 2013
Messages
448
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,883
Office Version
  1. 365
Platform
  1. Windows
How can I ignore blanks in this formula (CC5:CF9).
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)),""))
when I hit save, the list will reverse (CB5:CB9), everything is correct, but the list reverses. How can I stop that?
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.
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
448
Office Version
  1. 2019
Platform
  1. Windows
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.
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
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,883
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

gtd526

Active Member
Joined
Jul 30, 2013
Messages
448
Office Version
  1. 2019
Platform
  1. Windows
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.
Works great. It doesn't reverse the order. Thank you.
 

Forum statistics

Threads
1,144,241
Messages
5,723,198
Members
422,482
Latest member
MacSapper

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
Top