XY Charts - not plotting zero or #N/A Points
 

Using Excel 2003

Have a range of data containing some "empty" cells. However, as they
come from a formula they aren't of course empty.

Am using this data to plot an X-Y chart where I want these "empty"
cells not to plot. Have set the tools-options-charts setting to Not
plotted.

Have read numerous other postings here and elsewhere which says to have
#N/A as my "empty" value. It still interpolates the points.

Seen a posting suggesting I use VBA but unless this is relatively
simple, is a bit beyond me (and can't find a posting with any code)

Any suggestions?

Thanks

Paul


--
paulch
------------------------------------------------------------------------
paulch's Profile: http://www.thecodecage.com/forumz/member.php?userid=588
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120953

XY Charts - not plotting zero or #N/A Points
 

Hi,

This timely post might help.
http://peltiertech.com/WordPress/min...g-empty-cells/

Not that even with #N/A the scatter line will not be broken, only the
data points are skipped.
http://www.andypope.info/charts/brokenlines.htm

The VBA would be something like,

Range("A1:A10").specialcells(xlcelltypeformulas,16 ).clear

which will clear all cells in the range with formula errors.

Cheers
Andy

paulch wrote:
Using Excel 2003

Have a range of data containing some "empty" cells. However, as they
come from a formula they aren't of course empty.

Am using this data to plot an X-Y chart where I want these "empty"
cells not to plot. Have set the tools-options-charts setting to Not
plotted.

Have read numerous other postings here and elsewhere which says to have
#N/A as my "empty" value. It still interpolates the points.

Seen a posting suggesting I use VBA but unless this is relatively
simple, is a bit beyond me (and can't find a posting with any code)

Any suggestions?

Thanks

Paul



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

XY Charts - not plotting zero or #N/A Points
 

Thanks Andy

Got a colleague to use your VBA and it works a treat

Thanks very much

Paul


Andy Pope;435754 Wrote:
Hi,

This timely post might help.
'Mind the Gap - Charting Empty Cells | PTS Blog'
(http://peltiertech.com/WordPress/min...g-empty-cells/)

Not that even with #N/A the scatter line will not be broken, only the
data points are skipped.
'AJP Excel Information - Broken lines'
(http://www.andypope.info/charts/brokenlines.htm)

The VBA would be something like,

Range("A1:A10").specialcells(xlcelltypeformulas,16 ).clear

which will clear all cells in the range with formula errors.

Cheers
Andy

paulch wrote:
Using Excel 2003

Have a range of data containing some "empty" cells. However, as they
come from a formula they aren't of course empty.

Am using this data to plot an X-Y chart where I want these "empty"
cells not to plot. Have set the tools-options-charts setting to Not
plotted.

Have read numerous other postings here and elsewhere which says to
have
#N/A as my "empty" value. It still interpolates the points.

Seen a posting suggesting I use VBA but unless this is relatively
simple, is a bit beyond me (and can't find a posting with any code)

Any suggestions?

Thanks

Paul



--

Andy Pope, Microsoft MVP - Excel
'AJP Excel Information' (http://www.andypope.info)


--
paulch
------------------------------------------------------------------------
paulch's Profile: http://www.thecodecage.com/forumz/member.php?userid=588
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120953