# Ignore blanks in formula

#### gtd526

##### Active Member
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,""),"")
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### jasonb75

##### Well-known Member
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
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
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
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.

#### gtd526

##### Active Member
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.

Replies
10
Views
177
Replies
5
Views
110
Replies
3
Views
78
Replies
2
Views
227
Replies
2
Views
256

1,147,747
Messages
5,742,960
Members
423,767
Latest member
dafydd_jwc

### 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.

### Which adblocker are you using?

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

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