+ Reply to Thread
Results 1 to 7 of 7

Don't want 0 values to plot on chart

  1. #1
    Registered User
    Join Date
    11-27-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2010
    Posts
    32

    Don't want 0 values to plot on chart

    I am not very savy with Excel and when I searched for answers I couldn't understand what they were saying so I am starting my own thread. I have a sheet in excel that has 4 columns in it (one column for each of our locations). I have 52 horizontal rows representing each week of the year. Each cell has a formula in it to pull the value from other sheets within the workbook. Those values then get plotted on my graph for each week. I am in week 45 which is plotted correctly, but all weeks after bring my line down to "0" because the formula is in the cell but I have not calculated the values yet. My formula for one of the cells look like this: =SUM('Southbury 4-2013'!L11). So basically, for our Southbury location, it is plotting the L11 value from the sheet that is titled Southbury 4-2013. If I haven't filled out Southbury 4-2013 sheet yet then it is plotting the number at 0 instead of just not plotting it at all (which is what I want). How can I fix this, if I can fix it at all? Thank you!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Don't want 0 values to plot on chart

    Hi and welcome to the forum

    either construct the formula to generate an ERROR answer, or hide the columns that you have now "reached" yet
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

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

    Re: Don't want 0 values to plot on chart

    Not sure why you are using the SUM formula if you are only referencing 1 cell.

    =IF('Southbury 4-2013'!L11="",NA(),'Southbury 4-2013'!L11)

    #N/A in a chart will suppress the data marker.

    BTW, any other error answer will be treated as text and plotted as zero
    Cheers
    Andy
    www.andypope.info

  4. #4
    Registered User
    Join Date
    11-27-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Don't want 0 values to plot on chart

    Thank you both. I am using the SUM because I am not very excel savvy and I honestly didn't know how to do it any other way lol.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Don't want 0 values to plot on chart

    BTW, any other error answer will be treated as text and plotted as zero
    Andy, thanks for that. I have used that in charts before, and by co-incidence always used #NA

    @ lanespoli Happy to help If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

  6. #6
    Registered User
    Join Date
    11-27-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Don't want 0 values to plot on chart

    It looks like it is still dipping my line down to 0?

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Don't want 0 values to plot on chart

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 12
    Last Post: 05-01-2013, 02:00 AM
  2. Plot points on a area chart or plot areas on a scatter chart
    By maggy in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 01-17-2012, 04:39 AM
  3. Line chart that does not plot zero values
    By TrolleyDude in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-25-2010, 07:08 AM
  4. [SOLVED] how do I setup a pie chart that does not plot zero values ?
    By Dimmy in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-24-2006, 07:50 AM
  5. [SOLVED] Unable to Plot Values on Chart
    By elusiverunner in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-11-2006, 12:20 AM

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