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

e.g. b10 =if(isna(vlookup(a10,table,2,false)),"",vlookup(a1 0,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.

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

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

"Billy Leung" <Billy wrote in message
...
e.g. b10
=if(isna(vlookup(a10,table,2,false)),"",vlookup(a1 0,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.

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

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)

--
Adam Vero
MCP, MOS Master, MLSS, CWNA
http://veroblog.wordpress.com
http://www.meteorit.co.uk


"Billy Leung" wrote:

e.g. b10 =if(isna(vlookup(a10,table,2,false)),"",vlookup(a1 0,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.

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

Adam,

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

Billy

"AdamV" wrote:

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)

--
Adam Vero
MCP, MOS Master, MLSS, CWNA
http://veroblog.wordpress.com
http://www.meteorit.co.uk


"Billy Leung" wrote:

e.g. b10 =if(isna(vlookup(a10,table,2,false)),"",vlookup(a1 0,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.