Variable X-Axis

[Bryan Harris]

I have a quality manager that has created an Excel 2000 Workbook that
calculated some deviations of some different machined parts. He is trying
to chart it out, but we would like to limit the x-axis to a range of points
checked. Unfortunately the range is not static. We can set the non-checked
parts column to any non-positive value or letter.

What we would like to do is set the x-axis to equal whatever cells have
positive values.
The total number may vary by part checked.
i.e Part 1
Column A/B/C
0/#NA/#NA
1/.0250/.0350
2/.0300/.0355

Part 2
Column A/B/C
0/#NA/#NA
0/#NA/#NA
0/#NA/#NA
0/#NA/#NA
1/.0250/.0350
2/.0300/.0355
3/.0350/.0325
4/.0300/.0300
5/.0450/.0450
6/.0425/.0450

We want to plot the x vaue based upon the positive A column. Is this even
possible? Do we have to create some sort of macro to delete or hide the
invalid rows that do not meet the A column requirement?

Thanks in advance,
Bryan

 

[Jon Peltier]

Bryan -

No need for macros; you can set up dynamic charts:

http://peltiertech.com/Excel/Charts/Dynamics.html

You can use COUNT to count the number of numerical values in a range (use column B,
not A), and COUNTA for the total number of non blank cells. If all the #N/A are at
the beginning, define named ranges as follows:

Insert menu > Names > Define (or CTRL+F3)

Name: UsableB
Refers To:
=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-1,0,-COUNT(Sheet1!$B:$B),1)
Click Add

Name: UsableA
RefersTo:
=OFFSET(UsableB,0,-1)
Click Add

Name: UsableC
RefersTo:
=OFFSET(UsableB,0,1)
Click Add

Now make a chart. In the Source Data step of the Wizard, or by choosing Source Data
from the Chart menu, click on the Series tab. For X values, enter

=Sheet1!UsableA

For Y values enter these for your two series:

=Sheet1!UsableB
=Sheet1!UsableC


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______