Leave a cell COMPLETELY blank if there is an error

[Guest]

My question is similar to many others in this discussion group, but I have
searched pretty thoroughly and not found the answer to my exact question...

What I want to do is to leave a cell completely blank if the result of an
ISERROR test (shown below) is true. I need that cell to be completely blank,
because in another cell I have an INDIRECT formula that relies on the COUNTA
function that refers to this first cell. If the IF(ISERROR...) formula
deposits a "" in a cell, it will be counted in the COUNTA formula, and
INDIRECT will pull the (nonexistent) value from this cell.

(This formula is on a sheet named calc)
=IF(ISERROR((raw!K285),"",(raw!J285-(raw!K285/raw!K273-1)*100))

=INDIRECT(CONCATENATE("'calc'!f",COUNTA(calc!$F:$F)+23))

Is there a way to do this? Thank you!

 

[Jon Peltier]

We've been asking for some kind of BLANK() or NULL() worksheet function for
some time. Haven't gotten it, so no, there's no way to do this. Unless of
course you want to use VBA to clear the cells with errors, and then if the
data changes, you need to repopulate the cells with formulas.

- Jon

 

[Guest]

If you don't want to count the pseudo-blank, why not use COUNT() rather than
COUNTA()?

 

[Guest]

Thanks Jon and Gary's Student -- the reason for not using COUNT() is that I
also have text in the column, which does not counted, but it is easier to
work around that so I will switch. Would be nice to have something that would
leave a cell totally blank.

 

[Guest]

If you have Text in some cells, then a different approach is needed:

In place of:
COUNTA(calc!$F:$F)
use:
65536-COUNTBLANK(calc!$F:$F)

 

[Jon Peltier]

I was going to say that "" isn't blank, and in fact, ISBLANK(A1) returns
FALSE if A1 contains "". However, COUNTBLANK(A1) returns 1 whether A1
contains "" or whether A1 is completely blank. Nice to know.

- Jon

 

[Guest]

The fact that ISBLANK() and COUNTBLANK() treat functions differently allow us
to count either way.