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

Controling how zero values are plotted in charts
 

Paul:

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

By going to toolsOptioncharts 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



"crawshawpaul" wrote in message
...
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

Controling how zero values are plotted in charts
 

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 toolsOptioncharts 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



"crawshawpaul" wrote in message
...
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