Return an absolutely empty cell ... but not ""

[Billy Leung]

e.g. b10 =if(isna(vlookup(a10,table,2,false)),"",vlookup(a10,table,2,false))
c10 contains 5%
d10 = b10*c10

In an "if" formula, I like to use "" to make all #n/a, #value and zero to be
disapeared. However, it returns #value when I need to further calculate with
reference those cell contain "" (e.g. d10). I've tested the cell with "" by
using "isblank", it is a non-blanked cell even it displays nothing.

If I do not like to make another "if formula" under colume d to fix it, what
formula should I use to return an absolutely empty cell (rather than "" or 0)
under column b.

 

[T. Valko]

what formula should I use to return an absolutely empty

cell (rather than "" or 0) under column b.

Not possible. Since the cell contains a formula it will never be empty
(unless you delete the formula).

What formulas are you using downstream that return #VALUE! ?

Biff

 

[AdamV]

Why not return a zero and then use a custom number format to suppress this,
such as the format
#;#;;@

The ;; contains no format for the zero position so it will disappear

Alternatively, keep your "" blank cell, then your formula in D could simply
contain =SUM(b10)*c10 since SUM is tolerant of non-numeric entries (or rather
it treats them as zero without giving an error)

 

[Billy Leung]

Adam,

I've originally setup a conditional formatting to do it, but your suggestion
is definitely a better solution. Thanks a lot.

Billy