how to set a cell to EMPTY in a function?

S

serdar

i write an if condition in a cell as:

= IF (1<5;"";15)

but the "" returns as a null string and the cell cannot be used as a number
cell.

I want to make the cell EMPTY but not a cell contains a NULL string.
 
S

serdar

I am quoting another thread for reply.
Is it now clear what i mean? Do you know what does "a function returns a
NULL value" is? Next time, think twice i advise:)



Frank News said:
As part of a formula (or as part of Copy->PasteSpecial, I need to
overwrite a column of cells with either data or leave empty.
Example, =(if a<>b,c,"") - where c is a number - doesn't work because
it puts a blank in the cell.
Example, =(if a<>b,c,0) - where c is a number - doesn't work because
it puts a zero in the cell.

I don't want to use Tools->Options to show the cells as empty rather
than
0.00 because that will be so for the whole sheet, not just the one
column I am worrying about.

I am trying to update a protected sheet automatically from a second
sheet and only the 2 columns I am supposed to enter data into are
unprotected (can be changed). I have the whole thing working now
with a macro except for the cells which are supposed to remain empty.
If I use "" then it messes up a subsequent formula, and if I use 0
then it looks wrong (I want to place the value in each cell of the
column only if it meets a certain condition, else leave it empty.

Is there a way to make a cell empty as opposed to just looking empty?
I tried to replace all 0 with <delete key> but that didn't work ...

Looking for ideas ...

TIA

Fran
Click to expand...

Hi Frank,

If I understand correctly, then you want a function to return a NULL
value.

Unfortunately, no such worksheet function exists. This has been, in
my opinion, a serious omission in excel for many years.

As you correctly point out, an empty string ("") is not the same thing
as nothing or a NULL value, and nor is the numeric value zero.

As a workaround, could you use 'Not applicable' perhaps?

=(if a<>b,c,NA())


This works well in charting situations for example.


HTH,

Alan.
 
D

Dave Peterson

You can't make a cell empty using a formula. The best you can do is make it
look empty (return "" like you did).

But maybe you can change the next formula:

If you used:
=a1+b1+C1+D1
you could use
=sum(a1:D1)

or
=n(a1)+n(b1)+n(c1)+n(d1)
(if your range isn't contiguous)
 
D

Dodo2u

i write an if condition in a cell as:

= IF (1<5;"";15)

but the "" returns as a null string and the cell cannot be used as a
number cell.

I want to make the cell EMPTY but not a cell contains a NULL string.
Click to expand...

How could you make a cell empty when you yourself put a formula in it?!!

Make it zero = IF (1<5;0;15) and suppress the zeros.
 
E

Earl Kiosterud

Serdar,

We've all been good boys and girls. But it doesn't look as if Santa is ever
going to give us a NULL keyword that would evaluate to the equivalent of an
empty cell.
 
D

Dave Peterson

And either conditional formatting to hide the 0?

or even a custom format like:
General;-General;
 
Top