Skipping Zero values for plots
 

Hi,

How do I make a chart such that if I have some zero values then those
dont get plotted (especially in case of Line charts). Rather if I have
values

Oct 56
Nov 0
Dec 100

Then I would have only a straight line from Oct to Dec. I might have
such zero value pattern for many data points.

Is this possible to achieve without any complicated manipulation (I
think I have one in mind).

regards,
HP
India

Skipping Zero values for plots
 

Two methods:
1) replace 0 (or blank) by =NA() which displays as #N?A
or
2) delete 0 in data; click chart; use Tools | Options and open Chart tab;
specify how missing data is to be treated
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Hari" wrote in message
oups.com...
Hi,

How do I make a chart such that if I have some zero values then those
dont get plotted (especially in case of Line charts). Rather if I have
values

Oct 56
Nov 0
Dec 100

Then I would have only a straight line from Oct to Dec. I might have
such zero value pattern for many data points.

Is this possible to achieve without any complicated manipulation (I
think I have one in mind).

regards,
HP
India

Skipping Zero values for plots
 

Bernard,

Thanks for your solution. I wuld get back in case of any issues.

Regards,
HP
India

Skipping Zero values for plots
 

Bernard,

I tried it right now and it wouldnt work for my case.

Replacing the values by NA would amount to "tampering" with my report.
In the same vein I cannot delete the zeroes in my data as they are
formulas and not punched in values. Do we have a way where we can read
the array of values from worksheet and this is given as an argument to
a formula which could return all the array values back with zero
replaced by missing values. That way one can use this function in the
series formula for chart.

Regards,
HP
India

Skipping Zero values for plots
 

Can you not create a separate set of cells, which can be 'tampered'
with, and use these for the charting data. This cells could be in cells
away from the main report or even on another sheet.

Cheers
Andy

Hari wrote:
Bernard,

I tried it right now and it wouldnt work for my case.

Replacing the values by NA would amount to "tampering" with my report.
In the same vein I cannot delete the zeroes in my data as they are
formulas and not punched in values. Do we have a way where we can read
the array of values from worksheet and this is given as an argument to
a formula which could return all the array values back with zero
replaced by missing values. That way one can use this function in the
series formula for chart.

Regards,
HP
India


--

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