+ Reply to Thread
Results 1 to 5 of 5

Line charts with 0 being displayed.

  1. #1
    Registered User
    Join Date
    03-16-2009
    Location
    Essex, England
    MS-Off Ver
    Excel XP, 2003 & 2010
    Posts
    30

    Line charts with 0 being displayed.

    I'm creating a tracker for client sales, cumulatively and weekly, so I have the layout with the dates (every friday) whereby I put in the current, year to date total unit sales and from there the table below does =if(j23="","",sum(j23-j22)), hence when no sales figures have been entered then the cell remains blank and when the new cumulative figure has been entered then it automatically puts in the difference between last weeks figure and this weeks.
    The problem with this is that when plotting a line chart for the period, any blank cells are shown as 0, rather than just not drawn. I've tried using NA() instead of blank but no joy.

    Edited to add: This is Excel 2002.

    Edit: Problem solved, see link below.
    Last edited by V8Maverick; 03-17-2009 at 10:51 AM. Reason: Problem Solved

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Line charts with 0 being displayed.

    Hi,

    try this:

    - select the chart (this is important)
    - click Tools - Options and select the Charts tab
    - in the Active Charts section at the top select "Not plotted (leave gaps)". If this option is grayed out, you have not selected the chart, or you are using a chart type that does not support gaps (some charts don't)
    - hit OK

    Hope that helps

  3. #3
    Registered User
    Join Date
    03-16-2009
    Location
    Essex, England
    MS-Off Ver
    Excel XP, 2003 & 2010
    Posts
    30

    Re: Line charts with 0 being displayed.

    Thanks for that Teylyn... but it doen't work Still shows a line of zeroes.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Line charts with 0 being displayed.

    The NA() should stop the line from zeroing although it will not leave a gap, regardless of the Plot empty cells setting.

    Can you post example workbook
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    03-16-2009
    Location
    Essex, England
    MS-Off Ver
    Excel XP, 2003 & 2010
    Posts
    30

    Re: Line charts with 0 being displayed.

    Now solved via

    http://www.excelforum.com/excel-char...s-to-zero.html

    Thanks all, I was just doing the NA() wrongly!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1