CODEWITHSUNDEEP
excelif-statement
DToX
DToX

Reputation: 1

Excel If statement needs to show blanks while being nested

I am writing a formula to see cells that are blank along with see the cells that have a numeric value such as 0,10,-100 etc.

I have the following code that works but if the cell is blank it shows "0" and I need that to show blank. I should note that I am using offsets to make it easier to duplicate the data into other rows and just use REPLACE to speed up the process.

=IF(B1=DATEVALUE("7/3/2020"),OFFSET('CEDAR RAPIDS'!R4,0,0),
IF(B1=DATEVALUE("7/10/2020"),OFFSET('CEDAR RAPIDS'!R25,0,0),
IF(B1=DATEVALUE("7/17/2020"),OFFSET('CEDAR RAPIDS'!R47,0,0),
IF(B1=DATEVALUE("7/24/2020"),OFFSET('CEDAR RAPIDS'!R69,0,0),
IF(B1=DATEVALUE("7/31/2020"),OFFSET('CEDAR RAPIDS'!R91,0,0),
IF(B1=DATEVALUE("8/7/2020"),OFFSET('CEDAR RAPIDS'!R112,0,0),
IF(B1=DATEVALUE("8/14/2020"),OFFSET('CEDAR RAPIDS'!R133,0,0),
IF(B1=DATEVALUE("8/21/2020"),OFFSET('CEDAR RAPIDS'!R154,0,0),
IF(B1=DATEVALUE("8/28/2020"),OFFSET('CEDAR RAPIDS'!R174,0,0),
IF(B1=DATEVALUE("9/4/2020"),OFFSET('CEDAR RAPIDS'!R195,0,0),
IF(B1=DATEVALUE("9/11/2020"),OFFSET('CEDAR RAPIDS'!R215,0,0),
IF(B1=DATEVALUE("9/18/2020"),OFFSET('CEDAR RAPIDS'!R235,0,0),
IF(B1=DATEVALUE("9/25/2020"),OFFSET('CEDAR RAPIDS'!R255,0,0),
IF(B1=DATEVALUE("10/02/2020"),OFFSET('CEDAR RAPIDS'!R275,0,0),
IF(B1=DATEVALUE("10/9/2020"),OFFSET('CEDAR RAPIDS'!R295,0,0),
IF(B1=DATEVALUE("10/16/2020"),OFFSET('CEDAR RAPIDS'!R314,0,0),
IF(B1=DATEVALUE("10/23/2020"),OFFSET('CEDAR RAPIDS'!R334,0,0),
IF(B1=DATEVALUE("10/30/2020"),OFFSET('CEDAR RAPIDS'!R354,0,0),
IF(B1=DATEVALUE("11/6/2020"),OFFSET('CEDAR RAPIDS'!R374,0,0),
IF(B1=DATEVALUE("11/13/2020"),OFFSET('CEDAR RAPIDS'!R394,0,0),
IF(B1=DATEVALUE("11/20/2020"),OFFSET('CEDAR RAPIDS'!R414,0,0),
IF(B1=DATEVALUE("11/27/2020"),OFFSET('CEDAR RAPIDS'!R434,0,0),
IF(B1=DATEVALUE("12/4/2020"),OFFSET('CEDAR RAPIDS'!R454,0,0),
IF(B1=DATEVALUE("12/11/2020"),OFFSET('CEDAR RAPIDS'!R474,0,0)))))))))))))))))))))))))

I found this code below works correctly but I cannot seem to nest it like the above without errors.

=IF(B1=DATEVALUE("7/3/2020"),IF(ISBLANK('CEDAR RAPIDS'!F4),"",OFFSET('CEDAR RAPIDS'!F4,0,0)))

Upvotes: 0

Views: 23

Answers (1)

teylyn
teylyn

Reputation: 35915

Format the result of the cells with a custom number format that suppresses zeroes, for example

0;-0;;@

By the way, if you want to check if the result of the OFFSET() is zero, then you need to check the result of the OFFSET(), not the anchor of the OFFSET().

There is bound to be a better formula for this scenario than this nested monster with Offset. Just ask.

Upvotes: 1

Related Questions