Skipping Zero values for plots

[Hari]

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

[Bernard Liengme]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Hari]

Bernard,

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

Regards,
HP
India

[Hari]

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]

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