Controling how zero values are plotted in charts

[Guest]

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

 

[Kelly O'Day]

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/ExcelTips/MissingData.htm


...Kelly
(e-mail address removed)

 

[Guest]

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