Empty (NUll) value in cell, not empty string?

I have cell (D1) with this formula:

=IF(ISNA(VLOOKUP(C1,Navne,2,FALSE)),"";VLOOKUP(C1, Navne,2,FALSE))

If C1 is N/A, it returns an empty string. The problem is, you cant multiply
an empty string.

So my next formula =D1*10 it gives me a VALUE error. I would like to give me
a 0 in stead. I know I can change ti formula to insert a zero instead of the
"", but is there other way you can do it? I would like to avouid the
zero-value in D1.

I know I can make conditonal formatting of D1, or suppress the showing of
zeros.

Jan

I forgot to say, the reason I cannot use a zero in stead of the "" is,
thatzero is one of the possible return values from the VLOOKUP.

Jan



"Jan Kronsell" <kronsell(removebeforesend)@adslhome.dk> skrev i en
meddelelse news:uN4epuxzEHA.2676@TK2MSFTNGP12.phx.gbl...
> I have cell (D1) with this formula:
>
> =IF(ISNA(VLOOKUP(C1,Navne,2,FALSE)),"";VLOOKUP(C1, Navne,2,FALSE))
>
> If C1 is N/A, it returns an empty string. The problem is, you cant
multiply
> an empty string.
>
> So my next formula =D1*10 it gives me a VALUE error. I would like to give
me
> a 0 in stead. I know I can change ti formula to insert a zero instead of
the
> "", but is there other way you can do it? I would like to avouid the
> zero-value in D1.
>
> I know I can make conditonal formatting of D1, or suppress the showing of
> zeros.
>
> Jan
>
>

One way:

=N(D1)*10


In article <uN4epuxzEHA.2676@TK2MSFTNGP12.phx.gbl>,
"Jan Kronsell" <kronsell(removebeforesend)@adslhome.dk> wrote:

> I have cell (D1) with this formula:
>
> =IF(ISNA(VLOOKUP(C1,Navne,2,FALSE)),"";VLOOKUP(C1, Navne,2,FALSE))
>
> If C1 is N/A, it returns an empty string. The problem is, you cant multiply
> an empty string.
>
> So my next formula =D1*10 it gives me a VALUE error. I would like to give me
> a 0 in stead. I know I can change ti formula to insert a zero instead of the
> "", but is there other way you can do it? I would like to avouid the
> zero-value in D1.
>
> I know I can make conditonal formatting of D1, or suppress the showing of
> zeros.

For your second formula...

=IF(ISNUMBER(D1),D1*10,0)

Hope this helps!

Jan Kronsell Wrote:
> I have cell (D1) with this formula:
>
> =IF(ISNA(VLOOKUP(C1,Navne,2,FALSE)),"";VLOOKUP(C1, Navne,2,FALSE))
>
> If C1 is N/A, it returns an empty string. The problem is, you can
> multiply
> an empty string.
>
> So my next formula =D1*10 it gives me a VALUE error. I would like t
> give me
> a 0 in stead. I know I can change ti formula to insert a zero instea
> of the
> "", but is there other way you can do it? I would like to avouid the
> zero-value in D1.
>
> I know I can make conditonal formatting of D1, or suppress the showin
> of
> zeros.
>
> Ja

--
Domeni
-----------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...fo&userid=1078
View this thread: http://www.excelforum.com/showthread.php?threadid=31902

Thanks. Thatd id the trick.

Jan

"Domenic" <Domenic.1g13tf@excelforum-nospam.com> skrev i en meddelelse
newsomenic.1g13tf@excelforum-nospam.com...
>
> For your second formula...
>
> =IF(ISNUMBER(D1),D1*10,0)