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

B

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

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
 
A

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)
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top