If I plot a line chart on a series of data and leave a blank in the row the graph leaves a corresponding gap in the line.
I am trying to replicate this effect when the data series comes via formulae. I have tried getting the formula to return an empty string ("") and various error values but in each case the graph either directly connects the adjacent values or connects them via 0.
Anyone any ideas? (without using VBA)
Tools>Options>Chart - plot empty cells as: not plotted (leave gaps)
(using xl2k2)
HTH
My problem is I am plotting a data series which I want to be like such:
=IF(A1=condition, A1, leave gap)
and can't work how to do this without using VBA, or if it is not possible. (i.e. I have a data series in row 1, but where the data points meet certain conditions I want Excel to leave a gap even though there is data in the original series.)
Also I don't know how mnay such gaps there will be, so I can't just seperate the data into seperate series and plot them that way.
Thanks, Dzinja
Not sure then - even if you return "" via a formula the cell isn't blank as you've a formula there (as you already know). Hmmm... I'll have a ponder
Return NA() in the formula and it will not plot.
QuoteOriginally posted by Derk
Return NA() in the formula and it will not plot.
I thought NA() would interpolate rather than leave blank??? Might be wrong though...
As you said earlier, it depends on the setting. Tools>Options>Chart - plot empty cells as: not plotted (leave gaps) will let the NA() leave gaps.
Sorry Derk - obviously I have a goldfish's memory!
I don't think I explained myself.
When I use NA() Excel leaves a gap in the sense that it does not plot that particular point, but the line chart directly connects the points either side.
However, when the cell is genuinely blank Excel leaves a real gap - in effect the chart looks like two seperate lines, and this is the effect I wish to replicate.
Dz
Did you set Tools>Options>Chart - plot empty cells as: not plotted (leave gaps)?
I have gaps when I use the NA function (Excel 97). Maybe it's an Excel version thing.
I have got that set, but I'm using 2002 SP-2. Will try at home where I've both 97 and 2000 to make sure. Thanks, Dzinja.
If you are still having problems then take a look at my example.
http://www.andypope.info/charts/brokenlines.htm
Cheers
Andy
Thanks Andy - I think this is going to have to be the way forward.
Dzinja
Hopefully your data works with the masking technique.
I must point out though, as it has just be drawn to my attention, that the mask line will not work probably if you have two values between a =NA() gap.
To get around this you need yet another dummy series that draws the line back in again!
As soon as I can I will update my example page to reflect this new information.
Cheers
Andy
Thanks for the info - have managed to adapt for my purposes.
Dzinja
Don’t have an account yet? Register yourself now and be a part of our community!
