Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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: chart I have

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

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?

share|improve this question
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
up vote 3 down vote accepted

Create an IF function to place an empty value in the cell if it is zero.

IF(function <> 0, function, "")

I know this works in Excel, but not sure about OpenOffice. I am about 95% sure though.

share|improve this answer
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
Glad you figured it out. – wbeard52 Sep 7 '11 at 5:03

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.