Null Value Entered in a Cell by a Function

  • Thread starter Thread starter Mark T
  • Start date Start date
M

Mark T

How do I get an Excel Function to place a NULL value in a
cell vs. a blank or space. Here an example: Cell A1
contains the number 11, when I enter the formula =IF
(A1>10,"","text") it results in a blank value in the cell
vs. NULL or nothing. This is a problem when I am charting
the values because the chart interprets the value of a
cell containing double quotes "" as a 0 and charts the 0,
when what I want is for that cell not to chart at all,
which would happen if the cell were completely blank
(null). Does anyone know how to have an actual NULL value
entered in the cell vs. a "" or blank in a function?
 
Mark T said:
How do I get an Excel Function to place a NULL value in a
cell vs. a blank or space. Here an example: Cell A1
contains the number 11, when I enter the formula =IF
(A1>10,"","text") it results in a blank value in the cell
vs. NULL or nothing. This is a problem when I am charting
the values because the chart interprets the value of a
cell containing double quotes "" as a 0 and charts the 0,
when what I want is for that cell not to chart at all,
which would happen if the cell were completely blank
(null). Does anyone know how to have an actual NULL value
entered in the cell vs. a "" or blank in a function?
Click to expand...

You can't get a formula to return anything that will be interpreted as a
blank cell.
However, you can make it return #N/A:
=IF(A1>10,NA(),"text")
This will not get charted.
 
When you enter "" in an IF fuction the formular
understands anything between them as text and not
numbers, if you want 0 if true remove "" and enter 0.
 
Try this:
Click on the chart to select it.
On the menu bar go to Tools - Options.
On the Chart tab, choose the option you want under Plot
Empty Cells As

Good Luck!
 
In an IF function everything that is in "" is regarded as
text.
Cell value of A1= 11
=IF(A1>10,"","Text") The result:Text
Or
=If(A1>10,0,"") The result:0
 
As far as I know the only way to do this for charts is to
enter

=if(a1>10," ","TEXT")

this will give you a blank cell that only contains a
space and no data. If you have a dependant formula on
that cell it will return with #Value! error message.


Jez
 
Thanks for the ideas. Unfortunatley "" and 0 behave the
same as far as the chart is concerned. What I am looking
for is something that works like:

=if(A1>10,{NULL},"text") or
=if(A1>10,NULL,"text") or
=if(A1>10,[NULL],"text")

I just can't figure out how to represent the null. In
older versions of Excel and Lotus you used to be able to
represent a null value with {NULL}, and it worked inside
formulas. Any other thoughts? Was this removed in newer
versions of Excel?
 
Why don't you try the suggestion you are answering to? Paul suggested

=IF(A1>10,NA(),"text")

will return #N/A and it will be the same as null, if you dislike the look of
#N/A use conditional formatting and
formula is and

=ISNA(A1)

format, then fonts and white fonts..
--

Regards,

Peo Sjoblom

Mark T said:
Thanks for the ideas. Unfortunatley "" and 0 behave the
same as far as the chart is concerned. What I am looking
for is something that works like:

=if(A1>10,{NULL},"text") or
=if(A1>10,NULL,"text") or
=if(A1>10,[NULL],"text")

I just can't figure out how to represent the null. In
older versions of Excel and Lotus you used to be able to
represent a null value with {NULL}, and it worked inside
formulas. Any other thoughts? Was this removed in newer
versions of Excel?

-----Original Message-----


You can't get a formula to return anything that will be interpreted as a
blank cell.
However, you can make it return #N/A:
=IF(A1>10,NA(),"text")
This will not get charted.


.
Click to expand...
Click to expand...
 
Thanks for the ideas. Unfortunatley "" and 0 behave the
same as far as the charting is concerned. What I am
looking for is something that works like:

=if(A1>10,{NULL},"text") or
=if(A1>10,NULL,"text") or
=if(A1>10,[NULL],"text")

I just can't figure out how to represent the null. In
older versions of Lotus, and I think Excel, you used to
be able to represent a null value with {NULL}, and it
worked inside formulas. Any other thoughts? Was this
capability removed in newer versions of Excel?
 
=IF(A1>10,NA(),"text")
will do the job, if I have understood you correctly.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

Mark T said:
Thanks for the ideas. Unfortunatley "" and 0 behave the
same as far as the charting is concerned. What I am
looking for is something that works like:

=if(A1>10,{NULL},"text") or
=if(A1>10,NULL,"text") or
=if(A1>10,[NULL],"text")

I just can't figure out how to represent the null. In
older versions of Lotus, and I think Excel, you used to
be able to represent a null value with {NULL}, and it
worked inside formulas. Any other thoughts? Was this
capability removed in newer versions of Excel?

-----Original Message-----
How do I get an Excel Function to place a NULL value in a
cell vs. a blank or space. Here an example: Cell A1
contains the number 11, when I enter the formula =IF
(A1>10,"","text") it results in a blank value in the cell
vs. NULL or nothing. This is a problem when I am charting
the values because the chart interprets the value of a
cell containing double quotes "" as a 0 and charts the 0,
when what I want is for that cell not to chart at all,
which would happen if the cell were completely blank
(null). Does anyone know how to have an actual NULL value
entered in the cell vs. a "" or blank in a function?


.
Click to expand...
Click to expand...
 
Leo,
I tried NA() like you suggested but it drops in the text
value of "#N/A" in the field and then #N/A is was gets
charted. Still struggleing with this one. Any other ideas?

-Mark
-----Original Message-----
=IF(A1>10,NA(),"text")
will do the job, if I have understood you correctly.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

"Mark T" <[email protected]> skrev i en meddelelse
Thanks for the ideas. Unfortunatley "" and 0 behave the
same as far as the charting is concerned. What I am
looking for is something that works like:

=if(A1>10,{NULL},"text") or
=if(A1>10,NULL,"text") or
=if(A1>10,[NULL],"text")

I just can't figure out how to represent the null. In
older versions of Lotus, and I think Excel, you used to
be able to represent a null value with {NULL}, and it
worked inside formulas. Any other thoughts? Was this
capability removed in newer versions of Excel?

-----Original Message-----
How do I get an Excel Function to place a NULL value
Click to expand...
in
a
cell vs. a blank or space. Here an example: Cell A1
contains the number 11, when I enter the formula =IF
(A1>10,"","text") it results in a blank value in the cell
vs. NULL or nothing. This is a problem when I am charting
the values because the chart interprets the value of a
cell containing double quotes "" as a 0 and charts the 0,
when what I want is for that cell not to chart at all,
which would happen if the cell were completely blank
(null). Does anyone know how to have an actual NULL value
entered in the cell vs. a "" or blank in a function?


.
Click to expand...
Click to expand...


.
Click to expand...
 
Thanks for the ideas. Unfortunatley "" and 0 behave the
same as far as the chart is concerned. What I am looking
for is something that works like:

=if(A1>10,{NULL},"text") or
=if(A1>10,NULL,"text") or
=if(A1>10,[NULL],"text")

I just can't figure out how to represent the null. In
older versions of Excel and Lotus you used to be able to
represent a null value with {NULL}, and it worked inside
formulas. Any other thoughts? Was this removed in newer
versions of Excel?
Click to expand...

This has *NEVER* be a feature in any version of Excel that I've used (from
version 2 through 2000 for Windows, maybe it was possible in the original
version 1 that only ran on Macs, but I doubt it). FWLIW, this also can't be done
in any version of Lotus 1-2-3.

It seems your memory is faulty.

The only way to do what you seem to want to do involves using NA() or #N/A (the
constant error value returned by the NA function) to represent blank y values.
THERE IS NO ALTERNATIVE!
 
Mark T said:
I tried NA() like you suggested but it drops in the text
value of "#N/A" in the field and then #N/A is was gets
charted. Still struggleing with this one. Any other ideas?
Click to expand...

This is highly nonstandard behavior. Your formula *should* evaluate to #N/A,
which appears as text but is actually an error value constant. However, #N/A
wouldn't be charted normally. If Excel is charting it for you, is it
treating the same as zero?
 
It got it to work now. The NA() returns a #N/A value and
that DOES NOT get charted as you point out. Not sure what
I was doing wrong initially. Thanks!
 
It got it to work now. The NA() returns a #N/A value and
that DOES NOT get charted as you point out. Not sure what
I was doing wrong initially. Thanks!
 
Back
Top