+ Reply to Thread
Results 1 to 7 of 7

Zero Values in Graph not to be plotted

  1. #1
    Forum Contributor
    Join Date
    05-19-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    111

    Zero Values in Graph not to be plotted

    Hi
    I have made a graph with datas. This is a Line Chart with marker in it. The problem is whenever i have zero values. it plots zero. Is it possible to remove the zero plotting. This chart is for datas from 1st to 30th of month and if today is 12 the rest 18 days we can see the points plotted in zero.
    Pls help. The formula in my =SUM('1'!$D$152:$D$158).

    Fasial

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

    Re: Zero Values in Graph not to be plotted

    You need to alter your formula. You have not provided enough information for me to give the exact formula but it should be along the lines of

    =IF('test for valid data', SUM('valid data'), NA() )

    The #N/A data points will not plot a data marker. If all the end data points are #N/A then the line will also not be plotted.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Contributor
    Join Date
    05-19-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    111

    Re: Zero Values in Graph not to be plotted

    Dear Andy
    Yes it worked for the MArker. But what about the lines. How can we get rid of the lines as well

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

    Re: Zero Values in Graph not to be plotted

    Only way to not plot a line is to have a truly empty cell.
    If the N/As are at the end then the line should not appear. Post example if otherwise.

  5. #5
    Forum Contributor
    Join Date
    05-19-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    111

    Re: Zero Values in Graph not to be plotted

    Dear Andy
    Pls see the attached file. I have made 2 Graphs. I need the graph to look like the below one where the lines are not shown.
    Pls help
    Attached Files Attached Files

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

    Re: Zero Values in Graph not to be plotted

    As I said then point would need to be "at the end"
    Otherwise only way is to clear the contents of the cells with code.

  7. #7
    Forum Contributor
    Join Date
    05-19-2010
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    111

    Re: Zero Values in Graph not to be plotted

    What is the code which we can give?

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

    Re: Zero Values in Graph not to be plotted

    something like

    Please Login or Register  to view this content.
    which will clear all formula error cells in the selection.

+ 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