# Skipping Zero values for plots

1. ## 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

2. ## Re: 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" <excel_hari@yahoo.com> wrote in message
> 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
>

3. ## Re: Skipping Zero values for plots

Bernard,

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

Regards,
HP
India

4. ## Re: 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

5. ## Re: 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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1