No, you are not missing anything. XL will only interpolate over NA()s.
It won't create gaps. To get gaps, you have to use a programmatic
solution such as
Chart gap for N/A
http://www.tushar-mehta.com/excel/so...ity/index.html
However, because of a bug introduced with XL2002 (2000?) and not yet
fixed means even the code doesn't work with a line chart, though it
continues to work just fine with a XY Scatter chart.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
In article ,
says...
Hi Jon,
Using a standard line chart, I seem to be getting an interpolated value with
NA() as opposed to a gap in the line. Could I be missing a setting somewhere?
For example, my original data is on the left side below and charted data is
on the right. Assuming a blank column between the two sets with the data
starting in cell A1, I've added this formula to cell E1 and copied down the
column:
=IF(B10,B1,NA())
a 6 a 6
b 4 b 4
c 0 c #N/A
d 5 d 5
e 0 e #N/A
f 3 f 3
Excel seems to interpolate the line in column E rather than leaving a gap.
Tushar Mehta has this information on his site - does this still apply?
http://www.tushar-mehta.com/excel/so...discontinuity/
Thanks.
John Mansfield
"Jon Peltier" wrote:
Phil -
Change this:
If(m280,n28,0)
to this:
If(m280,n28,NA())
This results in the ugly #N/A error in the cell, but it makes the chart
ignore the point. Debra Dalgleish shows how to hide the ugliness with
conditional formatting:
http://contextures.com/xlCondFormat03.html#Errors
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Phil Lavis wrote:
Using Excel 2003. I have a data range for a graph. The values in the cells
are the results of a simple If function - If(m280,n28,0). The results are
taken from a larger data input exercise. But, the graph line (a simple
graph!) plots the FALSE value (0) when I would like there to really be no
value & hence no plotted point if the result is FALSE.