line chart with NA() values

12 month line chart, with some values being 0.

I am using an if statement that turns any 0 values to #N/A so they do
not show on the graph (which is what I want).

My problem arises when the 0 values fall in the middle of my data.

So for example:
1) data for all months (Jan-Dec), the line shows across all 12
months;
2) I have data for only 6 months (Jul-Dec), the line starts in Jul and
ends in Dec (perfect);
3) When I have data from Jan-Mar, and Oct-Dec, the line connects
between Mar and Oct. I want 2 distinct lines with no line where there
is no data (#N/A).

Any suggestions?


--
grime
------------------------------------------------------------------------
grime's Profile: http://www.excelforum.com/member.php...o&userid=19227
View this thread: http://www.excelforum.com/showthread...hreadid=477328

There is not cell value that behaves exactly like an empty cell. You
can delete the cell formulas (if a dynamic chart you could write a macro
to delete or restore the formula as apprpriate).

Jerry

grime wrote:

12 month line chart, with some values being 0.

I am using an if statement that turns any 0 values to #N/A so they do
not show on the graph (which is what I want).

My problem arises when the 0 values fall in the middle of my data.

So for example:
1) data for all months (Jan-Dec), the line shows across all 12
months;
2) I have data for only 6 months (Jul-Dec), the line starts in Jul and
ends in Dec (perfect);
3) When I have data from Jan-Mar, and Oct-Dec, the line connects
between Mar and Oct. I want 2 distinct lines with no line where there
is no data (#N/A).

Any suggestions?

Hi,

This work around might suffice.
http://www.andypope.info/charts/brokenlines.htm

Cheers
Andy

grime wrote:
12 month line chart, with some values being 0.

I am using an if statement that turns any 0 values to #N/A so they do
not show on the graph (which is what I want).

My problem arises when the 0 values fall in the middle of my data.

So for example:
1) data for all months (Jan-Dec), the line shows across all 12
months;
2) I have data for only 6 months (Jul-Dec), the line starts in Jul and
ends in Dec (perfect);
3) When I have data from Jan-Mar, and Oct-Dec, the line connects
between Mar and Oct. I want 2 distinct lines with no line where there
is no data (#N/A).

Any suggestions?



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Awesome workaround, and good additional information there. Thanks for
passing this one along!


--
grime
------------------------------------------------------------------------
grime's Profile: http://www.excelforum.com/member.php...o&userid=19227
View this thread: http://www.excelforum.com/showthread...hreadid=477328