Thread: Controling how zero values are plotted in charts

Can anybody advise me how to overcome the following:
I am trying to create a chart whereby the source data is calculated from a
formula.
I do not want zero values to be plotted in the chart.
I have tried the following, going to TOOLS, OPTIONS, CHARTS & then selecting
'plot empty cells as' = Not Plotted(leave gaps), but this does not overcome
the problem. This function only seems to work when data is not related to a
formula?
I would be very grateful for any help that anybody can give

Kind Regards Paul

Paul:

This is a very frequent question. You are half way to your answer.

By going to tools>Option>charts and selecting Not Plotted, you told excel
what to do with empty cells.

Cells with formulas are not empty. When you use the If - "" workaround, the
cell looks empty, however, Excel still sees the formula and tries to resolve
what to plot.

The full workaround when you have formulas is:

= If(C2<>"", c2*d2, Na())

You'll need to adjust the formula to your conditions, the basic idea is the
same. If the value in a test cell not empty , then use formula, if it is
empty, place Na() in cell.

Excel enters a #N/A in those cells that do not have a value in the test
cell. Excel recognizes #N/A and will not plot cells with this value.


There are several on-line tutorials available - including:

http://processtrends.com/pg_charts_missing_data.htm

http://www.stfx.ca/people/bliengme/E...issingData.htm


...Kelly
koday@processtrends.com


"crawshawpaul" <crawshawpaul@discussions.microsoft.com> wrote in message
news:F5592C3F-7F8E-4BAA-8FE8-741083BBF11C@microsoft.com...
> Can anybody advise me how to overcome the following:
> I am trying to create a chart whereby the source data is calculated from a
> formula.
> I do not want zero values to be plotted in the chart.
> I have tried the following, going to TOOLS, OPTIONS, CHARTS & then
> selecting
> 'plot empty cells as' = Not Plotted(leave gaps), but this does not
> overcome
> the problem. This function only seems to work when data is not related to
> a
> formula?
> I would be very grateful for any help that anybody can give
>
> Kind Regards Paul

Hi Kelly

Firstly thanks for your reply, secondly your talking to a total novice with
regards to excel.
I know its alot to ask but can you please explain step by step how to modify
my current formula without messing it all up

Very best regards Paul

"Kelly O'Day" wrote:

> Paul:
>
> This is a very frequent question. You are half way to your answer.
>
> By going to tools>Option>charts and selecting Not Plotted, you told excel
> what to do with empty cells.
>
> Cells with formulas are not empty. When you use the If - "" workaround, the
> cell looks empty, however, Excel still sees the formula and tries to resolve
> what to plot.
>
> The full workaround when you have formulas is:
>
> = If(C2<>"", c2*d2, Na())
>
> You'll need to adjust the formula to your conditions, the basic idea is the
> same. If the value in a test cell not empty , then use formula, if it is
> empty, place Na() in cell.
>
> Excel enters a #N/A in those cells that do not have a value in the test
> cell. Excel recognizes #N/A and will not plot cells with this value.
>
>
> There are several on-line tutorials available - including:
>
> http://processtrends.com/pg_charts_missing_data.htm
>
> http://www.stfx.ca/people/bliengme/E...issingData.htm
>
>
> ...Kelly
> koday@processtrends.com
>
>
> "crawshawpaul" <crawshawpaul@discussions.microsoft.com> wrote in message
> news:F5592C3F-7F8E-4BAA-8FE8-741083BBF11C@microsoft.com...
> > Can anybody advise me how to overcome the following:
> > I am trying to create a chart whereby the source data is calculated from a
> > formula.
> > I do not want zero values to be plotted in the chart.
> > I have tried the following, going to TOOLS, OPTIONS, CHARTS & then
> > selecting
> > 'plot empty cells as' = Not Plotted(leave gaps), but this does not
> > overcome
> > the problem. This function only seems to work when data is not related to
> > a
> > formula?
> > I would be very grateful for any help that anybody can give
> >
> > Kind Regards Paul
>
>
>