How can I keep the chart from showing "0" on linked data points

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am setting up a workbook with several sheets. One sheet one I am entering data with formulas to sum. On sheet two I am linking the sums to generate a running chart. All works well except the sums of columns with no data in them report "0" on the chart data cells causing the chart to show the graph line dropping to 0. I am trying to find out how to keep the chart from showing any data points with "0" in the data cell. In other words, I want the chart to grow as I add data to sheet one without data points on the chart until I enter the data. Thanks for any suggestions.
 
ahmayzin said:
I am setting up a workbook with several sheets. One sheet one I am
Click to expand...
entering data with formulas to sum. On sheet two I am linking the sums to
generate a running chart. All works well except the sums of columns with no
data in them report "0" on the chart data cells causing the chart to show
the graph line dropping to 0. I am trying to find out how to keep the chart
from showing any data points with "0" in the data cell. In other words, I
want the chart to grow as I add data to sheet one without data points on the
chart until I enter the data. Thanks for any suggestions.
--
Click to expand...
ahmayzin,

The help says to select the chart, then go to
Tools|Options|Chart|Plot Empty Cell As
and set it to Not.

It doesn't work for me. 8:-(
So, I'm curious if it works for you.

HTH,
Brad.
 
Brad -

The cells aren't empty if they contain a formula that only makes the
cell appear empty. There's no worksheet function for EMPTY() or BLANK()
or NULL(), so Debra's suggestion to use NA() is the best you can get.

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

The cells aren't empty if they contain a formula that only makes the
cell appear empty. There's no worksheet function for EMPTY() or BLANK()
or NULL(), so Debra's suggestion to use NA() is the best you can get.
Click to expand...
Got it.

This works great for a line style graph, but not for an area graph.
Those still dive to 0 on NA.

Thanks,
Brad.
 
Brad -

The real answer to the OP's question,
In other words, I want the chart to grow as I add data to sheet one
without data points on the chart until I enter the data. Thanks for
any suggestions.
Click to expand...

is to make a chart based on dynamic ranges. There are some examples and
a lot of links on my web site:

http://peltiertech.com/Excel/Charts/Dynamics.html

For your area chart, you could use a dynamic range to cut off points
beyond the end of the data, and use some kind of interpolation formula
for any zeros within the range.

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