software

Is it possible to have Excel read an empty cell as quot;no valuequot; rather
than the value=0?
I have a graph and corresponding range with values and somtimes cells
in this range should be read as a quot;blankquot; cell! Resulting in *-no
point-* in the graph.
Assume that I have, in cell C5, the following function:
IF(AND(ISNUMBER($A5);ISNUMBER($B$4));$A5*$B$4;quot; quot;).
If, in cell A5, I have quot;-quot; (a dash) = no value (no reading), this will
result in C5 being a Blank cell, due to the quot; quot; in the IF-function.
C5 is copied and pasted into the range connected to my graph. I want it
to result in no point in the graph!
But the graph is reading this cell as if the value was 0.0!
Is there a way to avoid this??
Your help is very much appreciated.--
ulfah
------------------------------------------------------------------------
ulfah's Profile: www.excelforum.com/member.php...oamp;userid=29204
View this thread: www.excelforum.com/showthread...hreadid=507215I'd add another column and use a formula like:

=if(b2=quot;quot;,na(),b2)
then use that column in the chart.

ulfah wrote:
gt;
gt; Is it possible to have Excel read an empty cell as quot;no valuequot; rather
gt; than the value=0?
gt; I have a graph and corresponding range with values and somtimes cells
gt; in this range should be read as a quot;blankquot; cell! Resulting in *-no
gt; point-* in the graph.
gt; Assume that I have, in cell C5, the following function:
gt; IF(AND(ISNUMBER($A5);ISNUMBER($B$4));$A5*$B$4;quot; quot;).
gt; If, in cell A5, I have quot;-quot; (a dash) = no value (no reading), this will
gt; result in C5 being a Blank cell, due to the quot; quot; in the IF-function.
gt; C5 is copied and pasted into the range connected to my graph. I want it
gt; to result in no point in the graph!
gt; But the graph is reading this cell as if the value was 0.0!
gt; Is there a way to avoid this??
gt; Your help is very much appreciated.
gt;
gt; --
gt; ulfah
gt; ------------------------------------------------------------------------
gt; ulfah's Profile: www.excelforum.com/member.php...oamp;userid=29204
gt; View this thread: www.excelforum.com/showthread...hreadid=507215

--

Dave Peterson

why add another column, when you can just replace quot; quot; with NA()?

quot;Dave Petersonquot; wrote:

gt; I'd add another column and use a formula like:
gt;
gt; =if(b2=quot;quot;,na(),b2)
gt; then use that column in the chart.
gt;
gt; ulfah wrote:
gt; gt;
gt; gt; Is it possible to have Excel read an empty cell as quot;no valuequot; rather
gt; gt; than the value=0?
gt; gt; I have a graph and corresponding range with values and somtimes cells
gt; gt; in this range should be read as a quot;blankquot; cell! Resulting in *-no
gt; gt; point-* in the graph.
gt; gt; Assume that I have, in cell C5, the following function:
gt; gt; IF(AND(ISNUMBER($A5);ISNUMBER($B$4));$A5*$B$4;quot; quot;).
gt; gt; If, in cell A5, I have quot;-quot; (a dash) = no value (no reading), this will
gt; gt; result in C5 being a Blank cell, due to the quot; quot; in the IF-function.
gt; gt; C5 is copied and pasted into the range connected to my graph. I want it
gt; gt; to result in no point in the graph!
gt; gt; But the graph is reading this cell as if the value was 0.0!
gt; gt; Is there a way to avoid this??
gt; gt; Your help is very much appreciated.
gt; gt;
gt; gt; --
gt; gt; ulfah
gt; gt; ------------------------------------------------------------------------
gt; gt; ulfah's Profile: www.excelforum.com/member.php...oamp;userid=29204
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=507215
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Sometimes, the look of the data is important and #n/a's are pretty ugly. And
sometimes those values could be used in other formulas and errors could mess up
those subsequent formulas.

They could be hidden with format|Conditional formatting, though. And other
formulas could be rewritten to avoid the errors.

(I'd just hide the whole helper column after I made the chart.)

But your post does show a problem with my formula.

I didn't notice that the OP used quot; quot; (I always use quot;quot; (no space)).

And the OP uses a semicolon instead.

I think that this formula is better:

=if(trim(b2)=quot;quot;;na();b2)

But I'd actually change the original formula:
=IF(AND(ISNUMBER($A5);ISNUMBER($B$4));$A5*$B$4;quot;quot;)

Those spaces are can be a problem.

Sloth wrote:
gt;
gt; why add another column, when you can just replace quot; quot; with NA()?
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; I'd add another column and use a formula like:
gt; gt;
gt; gt; =if(b2=quot;quot;,na(),b2)
gt; gt; then use that column in the chart.
gt; gt;
gt; gt; ulfah wrote:
gt; gt; gt;
gt; gt; gt; Is it possible to have Excel read an empty cell as quot;no valuequot; rather
gt; gt; gt; than the value=0?
gt; gt; gt; I have a graph and corresponding range with values and somtimes cells
gt; gt; gt; in this range should be read as a quot;blankquot; cell! Resulting in *-no
gt; gt; gt; point-* in the graph.
gt; gt; gt; Assume that I have, in cell C5, the following function:
gt; gt; gt; IF(AND(ISNUMBER($A5);ISNUMBER($B$4));$A5*$B$4;quot; quot;).
gt; gt; gt; If, in cell A5, I have quot;-quot; (a dash) = no value (no reading), this will
gt; gt; gt; result in C5 being a Blank cell, due to the quot; quot; in the IF-function.
gt; gt; gt; C5 is copied and pasted into the range connected to my graph. I want it
gt; gt; gt; to result in no point in the graph!
gt; gt; gt; But the graph is reading this cell as if the value was 0.0!
gt; gt; gt; Is there a way to avoid this??
gt; gt; gt; Your help is very much appreciated.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; ulfah
gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; ulfah's Profile: www.excelforum.com/member.php...oamp;userid=29204
gt; gt; gt; View this thread: www.excelforum.com/showthread...hreadid=507215
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson