IF function returning blank cells as 0 instead of a blank even though "" is being used

[scootty83]

I'm pulling data from a master workbook into several other workbooks and the formula finally seems to be working the way I need it to. However, even though I am using "" at the end of the IF formula to display a blank if the result is FALSE, I am getting zeros which is cluttering up the spreadsheet. I don't want to change Excel's preferences to not show zero values, zeros in some cases need to be seen. This is the formula I am using:

Please Login or Register  to view this content.

I have tried to wrap the whole formula with =IFERROR(aboveformula,"") but that didn't work. I have also tried to have it display "-" instead of "", but that didn't work either. My last resort is to do conditional formatting to turn the text white if it comes up as a zero, but again, sometimes a zero needs to be seen. I appreciate the help!

[TMS]

I guess you need to test the whole thing for a blank return.

Please Login or Register  to view this content.

committed with Ctrl-Shift-Enter.


Regards, TMS

[scootty83]

Your suggestion worked, Trevor. Thank you! I wonder why the formula wouldn't already provide a blank for an empty cell instead of a zero. This will still bother me even though it has been fixed.

[TMS]

You're welcome. Thanks for the rep.

I wonder why the formula wouldn't already provide a blank for an empty cell instead of a zero.

That's just what happens

If you leave cell A2 blank and put the formula =A2 in cell B2, you will get a zero returned. You would have to make it =IF(A2="","",A2). Same result if a VLOOKUP returns a blank ... you get zero.

Regards, TMS