getting a blank FALSE result in an IF formula?

[Robert Minneapolis]

I posted this as part of a question in another thread (http://www.mrexcel.com/board2/viewtopic.php?t=287795), but perhaps it would garner some answers as its own rephrased question...

Is using "" the most appropriate way to get a blank result as part of an IF formula? For example, right now the way I would write such a formula looks something like:

=IF(LEFT(A1,5)="PARTY","AWESOME","")

The problem that I am having is that this method seems to somehow mark the cell as nonblank, even when converting the formulas to straight values using 'Paste Special->Values'. For example, although a FALSE result appears blank, a COUNTA formula counts it as a nonblank cell. What gives? How can I avoid this?

any help would be appreciated,
Robert

 

[barry houdini]

Yes, that's the standard way to return a blank.

As you say, it then presents some problems because it isn't treated exactly like true blank cells.

COUNTBLANK function will, however, count both true blanks and "formula blanks".

If you want to count text (not numbers) but exclude formula blanks you can try

=COUNTIF(range,"*?")