[Solved] Charts : Leaving gaps in line charts

  • 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

    <a href="http://www.mrexcel.com/relayforlife.shtml" target="new"><img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"></a>

  • 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

    <a href="http://www.mrexcel.com/relayforlife.shtml" target="new"><img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"></a>

  • Quote

    Originally 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...

    <a href="http://www.mrexcel.com/relayforlife.shtml" target="new"><img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"></a>

  • Sorry Derk - obviously I have a goldfish's memory!

    <a href="http://www.mrexcel.com/relayforlife.shtml" target="new"><img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"></a>

  • 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

  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!