Blank cells in graph

[Kirsty W]

I have a a line graph that displays customer behaviour in a number of
categories. The data source for the graph is a table that refreshes every
time a different 'customer' is selected from a macro drop-down list above the
table, drawing data from another table.

All the blank cells from the table are plotted as zero on the line graph,
and I can't seem to change it so that the blank cells are not plotted on the
line graph at all. I've tried going to Tools>Options>Chart, but the 'Plot
empty cells as' options are all greyed out except for the 'Zero' option-
which I don't want!

Can anyone help......

Thanks

 

[David Biddulph]

Whatever formula you're using to generate your cell contents, if it's
currently setting the result to "", get it to set to NA() instead. [If you
then also want to use conditional formatting to make the N/A# cells look
empty, you can do so.]

 

[Kirsty W]

Hiya

Vlookup is getting the data into the table that feeds the line graph.

=VLOOKUP($B$2,ebookings!$A$4:H31,'Overview - by Cust'!H$99,FALSE)

Even if I put NA() in the cells that the Vlookup is using to get data, which
means NA() is appearing in the table from which the graph feeds, the options
to 'Plot Empty Cells' are still greyed out.

Whatever formula you're using to generate your cell contents, if it's
currently setting the result to "", get it to set to NA() instead. [If you
then also want to use conditional formatting to make the N/A# cells look
empty, you can do so.]
--
David Biddulph

I have a a line graph that displays customer behaviour in a number of
categories. The data source for the graph is a table that refreshes every
time a different 'customer' is selected from a macro drop-down list above
the
table, drawing data from another table.

All the blank cells from the table are plotted as zero on the line graph,
and I can't seem to change it so that the blank cells are not plotted on
the
line graph at all. I've tried going to Tools>Options>Chart, but the 'Plot
empty cells as' options are all greyed out except for the 'Zero' option-
which I don't want!

Can anyone help......

Thanks

 

[Jon Peltier]

If the cell contains a formula, it's not an empty cell. There's no way for a
formula to make Excel think it's an empty cell. The NA() is a trick that
works for line and XY charts, because it suppresses plotting of a point. It
is the equivalent of the Interpolate option of the Plot Empty Cells setting,
so a line connecting points passes across the gap.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Hiya

Vlookup is getting the data into the table that feeds the line graph.

=VLOOKUP($B$2,ebookings!$A$4:H31,'Overview - by Cust'!H$99,FALSE)

Even if I put NA() in the cells that the Vlookup is using to get data,
which
means NA() is appearing in the table from which the graph feeds, the
options
to 'Plot Empty Cells' are still greyed out.

Whatever formula you're using to generate your cell contents, if it's
currently setting the result to "", get it to set to NA() instead. [If
you
then also want to use conditional formatting to make the N/A# cells look
empty, you can do so.]
--
David Biddulph

I have a a line graph that displays customer behaviour in a number of
categories. The data source for the graph is a table that refreshes
every
time a different 'customer' is selected from a macro drop-down list
above
the
table, drawing data from another table.

All the blank cells from the table are plotted as zero on the line
graph,
and I can't seem to change it so that the blank cells are not plotted
on
the
line graph at all. I've tried going to Tools>Options>Chart, but the
'Plot
empty cells as' options are all greyed out except for the 'Zero'
option-
which I don't want!

Can anyone help......

Thanks

 

[Kirsty W]

Thanks David and Jon,

I've now got it to work!
How should I use the conditional formatting to make the #N/A cells look empty?

Thanks,

Whatever formula you're using to generate your cell contents, if it's
currently setting the result to "", get it to set to NA() instead. [If you
then also want to use conditional formatting to make the N/A# cells look
empty, you can do so.]
--
David Biddulph

I have a a line graph that displays customer behaviour in a number of
categories. The data source for the graph is a table that refreshes every
time a different 'customer' is selected from a macro drop-down list above
the
table, drawing data from another table.

All the blank cells from the table are plotted as zero on the line graph,
and I can't seem to change it so that the blank cells are not plotted on
the
line graph at all. I've tried going to Tools>Options>Chart, but the 'Plot
empty cells as' options are all greyed out except for the 'Zero' option-
which I don't want!

Can anyone help......

Thanks

 

[David Biddulph]

Formula is: =ISNA(A1)
--
David Biddulph

Thanks David and Jon,

I've now got it to work!
How should I use the conditional formatting to make the #N/A cells look
empty?

Whatever formula you're using to generate your cell contents, if it's
currently setting the result to "", get it to set to NA() instead. [If
you
then also want to use conditional formatting to make the N/A# cells look
empty, you can do so.]

....

 

[David Biddulph]

Formula is: =ISNA(A1)

.... and at this point I should have added:
make the font colour white (or whatever your cell background is).

--
David Biddulph

Thanks David and Jon,

I've now got it to work!
How should I use the conditional formatting to make the #N/A cells look
empty?

Whatever formula you're using to generate your cell contents, if it's
currently setting the result to "", get it to set to NA() instead. [If
you
then also want to use conditional formatting to make the N/A# cells look
empty, you can do so.]

...

 

[linda]

and soon if the data is available,it cannot be seen;-)
--
Regards,
Linda

Formula is: =ISNA(A1)

.... and at this point I should have added:
make the font colour white (or whatever your cell background is).

--
David Biddulph

Thanks David and Jon,

I've now got it to work!
How should I use the conditional formatting to make the #N/A cells look
empty?
:

Whatever formula you're using to generate your cell contents, if it's
currently setting the result to "", get it to set to NA() instead. [If
you
then also want to use conditional formatting to make the N/A# cells look
empty, you can do so.]

...

 

[David Biddulph]

Yes, perhaps (for the benefit of those not familiar with this use of CF) I
should have made it clear that the font colour white advice was meant to go
with the CF ISNA() condition, and not to be applied as the basic format for
the cell.
--
David Biddulph

and soon if the data is available,it cannot be seen;-)

Formula is: =ISNA(A1)

.... and at this point I should have added:
make the font colour white (or whatever your cell background is).

Thanks David and Jon,

I've now got it to work!
How should I use the conditional formatting to make the #N/A cells
look
empty?
:

Whatever formula you're using to generate your cell contents, if it's
currently setting the result to "", get it to set to NA() instead.
[If
you
then also want to use conditional formatting to make the N/A# cells
look
empty, you can do so.]
...