Solved

# Hide zero values on an X Y scatter graph

Posted on 2009-12-21
450 Views
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.

0
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.

sk
0

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.
0

## Featured Post

### Suggested Solutions

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 …