+ Reply to Thread
Results 1 to 8 of 8

How to make my line chart disregard Zero Values

  1. #1
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    How to make my line chart disregard Zero Values

    I was wondering how I can make my line chart disregard zero values?

    I don't mean empty cells that I can make the table disregard by editing the data!

    Thank you!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    43,311

    Re: How to make my line chart disregard Zero Values

    Hi again.

    Is the zero an entry, or a calc?
    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 some-1 helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to make my line chart disregard Zero Values

    Hi! Great to see you again 😊

    It is a calculation! Itís basically a summary of a chart I made.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    43,311

    Re: How to make my line chart disregard Zero Values

    If you force the calc to return ERROR, the chart will ignore it

  5. #5
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to make my line chart disregard Zero Values

    I'm not exactly sure how to do that so I have attached the excel sheet.

    I created a dynamic dropdrop list for the chart that is connected to the summary sheet, but the formula makes the empty cell come out as value 0.

    If you see on the chart, it makes a drop to 0...

    Is there a way I can make it disregard the 0 and connect from 1 to 3 right away?

    Thank you
    Attached Files Attached Files

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    18,066

    Re: How to make my line chart disregard Zero Values

    you need your formula in column N to return #N/A rather than zero when the cell in the lookup range is empty.

    Quickest way to do this is select the lookup range, CTRL+G > Special Cells > Blanks.
    type =NA()
    use CTRL+ENTER to populate all cells.
    Cheers
    Andy
    www.andypope.info

  7. #7
    Registered User
    Join Date
    06-07-2018
    Location
    Vancouver
    MS-Off Ver
    2016
    Posts
    43

    Re: How to make my line chart disregard Zero Values

    Thank you! It worked

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    43,311

    Re: How to make my line chart disregard Zero Values

    Seeing as this will be an ongoing file, it might be better to have that built into the formula, rather than manually changing the 0's (Andy's suggests are always great though). To that end, maybe this will work for you as well>
    N2=IF(INDEX($B$2:$J$73,MATCH(M2,$A$2:$A$73,0),MATCH($N$1,$B$1:$J$1,0))=0,#N/A,INDEX($B$2:$J$73,MATCH(M2,$A$2:$A$73,0),MATCH($N$1,$B$1:$J$1,0)))

+ 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