# How do I create a line graph which ignores zero values?

I want to create a graph showing trends over time, calculated from a formula of various data.

However, some data is missing, resulting in zeroes in the calculated data which the chart uses.

When plotting this data against as a line graph, it's drawing a line down to the axis, which I don't want.

I'd like either a gap in the line, or skip the data point and draw from directly previous to next.

i.e. given this data:

``````00.0    61.6    39.5    74.6    86.8    72.0    66.5    99.5    85.6    80.6    95.0    122.4
81.0    83.1    88.2    00.0    94.3    60.5    00.0    99.5    95.8    86.2    00.0    111.4
84.1    110.8   78.9    62.0    75.9    102.2   89.9    87.1    81.6    73.5    00.0    116.9
``````

All the 00.0 items mean "no data", so I'd like them ignored (not treated as 0 value).

This is the chart I currently have:

This is how I'd like the chart to come out:

Again, I don't mind if the dotted lines are blank or solid, so long as they don't go down to zero.

The data is dynamic/calculated, so I can't just say `=AVERAGE(prev,next)` since I don't know where missing data will occur in advance - I need a solution which takes this into account.

Any ideas?

-
I've just found that if I select a line, right click and select "Format Data Series", there is a "Plot options" which can handle missing values - "Leave gap", "Assume zero", "Continue line" - so I guess I need to find a function that can convert my zero to a missing value and then I'll have a solution. – Peter Boughton Sep 7 '11 at 3:34

Create an `IF` function to place an empty value in the cell if it is zero.
``````IF(function <> 0, function, "")
Yeah, I was just coming back to say I've basically done that. Well, I created a second set of cells for the graph and did `IF( A1<>0 , A1 , "" )` instead - is preferable to duplicating the formula with `IF( FORMULA<>0 , FORMULA , "" )` in this case - easier to fill down/right with cell references; would be more hassle having to copy & paste formulas (they're not identical). – Peter Boughton Sep 7 '11 at 3:52