Hide zero values on an X Y scatter graph

Posted on 2009-12-21
Last Modified: 2012-06-21
I have a dynamic XY chart.

Dates in COL A  and the values are in whatever column I offset to.   Some values are Zero.  I do not want to show the value on the chart as zero if there is no value.

I have tried using a custom # format.
I have tried using a formula  (probably didn't do this one correct)

How can I do this.  I want to show the gap in the line not have it drawn on the X axis as Zero.

thanks experts.

Question by:skillilea

    Author Comment

    Think I found a possible one.

    IF(A1 = 0 , #N/A, A1)

    any better ways so I don't have to add another IF in my formula.

    LVL 85

    Accepted Solution

    Not really - the only guaranteed solution is a truly blank cell. Using NA() will give you an interpolated value if it's in the middle of your data, whereas a blank cell will give a break in your line. It depends what you actually want plotted.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Running Out of IP Addresses 9 94
    Detect file exist or not 3 40
    Copy paste as values 4 33
    Complex Lookup Formulas 3 30
    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now