Avoid plotting unwanted values

[Jim]

Hi
I'm using Excel to plot math graphs, specifically numerical integrations.
Thanks to an earlier post from Jon Peltier I've found that it's better to use
the XY scatter chart rather than a simple line graph for this purpose -
although you lose the area fill-in feature, but that's a cosmetic problem!
Now can anyone help me to find a way of preventing Excel from plotting
unwanted values? The point here is that the table of X and Y value is set up
as 10000 row columns, but only part of this is used depending on the limits
of the integral and/or the size of the iterations, both of which are
adjustable by formula. So part of the X scale is often out of range, or not
required.
There is an option to leave blank cells which are empty, but since my cells
all have a formula in them, even when it's set by formula to zero or "",
Excel plots them as unwanted points on my graph!
Another way of solving this would be if I could use a formula to set the
Data Range, rather than just just fix it as $B$10:$C$10000.
The only way I've found to do this so far is to manually change the Data
Range each time I change the integral limits. As this needs to often, it's a
real drag!
Any advice greatly appreciated.

 

[John Mansfield]

One option might be to use the combination of the Excel functions OFFSET and
COUNTA within named ranges as chart sources. Doing so will allow your chart
to dynamically update.

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=518

Also, for certain conditions you can build "=NA()" into your data source
where you don't want that data to appear on your chart.

 

[Jim]

Thanks for that John,
I've tried the N/A() suggestion before, and again now, and it doesn't work
for me. My chart still reads the N/A() points as zeros and plots them. I've
set the 'Plot empty cells as (Not plotted, leave gaps)' in the Chart Options.
Am I missing something?
As for your first suggestion, I'm studying your article with much interest.
I think it will do what I want and opens up other possibilities. Many thanks!
Jim

 

[Jim]

Ah yes of course, NA() not N/A(), silly of me! This works now and answers my
question. I really appreciate your input.Now I've got an answer which works
and an interesting idea to follow up.
many thanks again!
Jim