Blank (empty) cell always equal to 0??

[ulfah]

Is it possible to have Excel read an empty cell as "no value" 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 "blank" 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;" ").
If, in cell A5, I have "-" (a dash) = no value (no reading), this will result in C5 being a Blank cell, due to the " " 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.

[Dave Peterson]

I'd add another column and use a formula like:

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

ulfah wrote:
>
> Is it possible to have Excel read an empty cell as "no value" 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 "blank" 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;" ").
> If, in cell A5, I have "-" (a dash) = no value (no reading), this will
> result in C5 being a Blank cell, due to the " " 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: http://www.excelforum.com/member.php...o&userid=29204
> View this thread: http://www.excelforum.com/showthread...hreadid=507215

--

Dave Peterson

[Sloth]

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

"Dave Peterson" wrote:

> I'd add another column and use a formula like:
>
> =if(b2="",na(),b2)
> then use that column in the chart.
>
> ulfah wrote:
> >
> > Is it possible to have Excel read an empty cell as "no value" 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 "blank" 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;" ").
> > If, in cell A5, I have "-" (a dash) = no value (no reading), this will
> > result in C5 being a Blank cell, due to the " " 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: http://www.excelforum.com/member.php...o&userid=29204
> > View this thread: http://www.excelforum.com/showthread...hreadid=507215
>
> --
>
> Dave Peterson
>

[Dave Peterson]

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 " " (I always use "" (no space)).

And the OP uses a semicolon instead.

I think that this formula is better:

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

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

Those spaces are can be a problem.

Sloth wrote:
>
> why add another column, when you can just replace " " with NA()?
>
> "Dave Peterson" wrote:
>
> > I'd add another column and use a formula like:
> >
> > =if(b2="",na(),b2)
> > then use that column in the chart.
> >
> > ulfah wrote:
> > >
> > > Is it possible to have Excel read an empty cell as "no value" 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 "blank" 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;" ").
> > > If, in cell A5, I have "-" (a dash) = no value (no reading), this will
> > > result in C5 being a Blank cell, due to the " " 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: http://www.excelforum.com/member.php...o&userid=29204
> > > View this thread: http://www.excelforum.com/showthread...hreadid=507215
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson