7

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?

1
  • 2
    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. Sep 7, 2011 at 3:34

2 Answers 2

4

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.

2
  • 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). Sep 7, 2011 at 3:52
  • Glad you figured it out.
    – wbeard52
    Sep 7, 2011 at 5:03
0

Can't you just use #n/a instead? I believe that would be easier.

1
  • This is more of a comment than an answer. Please flesh out your answer if you believe the #n/a is a preferable option. Jul 25, 2017 at 18:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .