Blank cells and Charting multiple data points

I have a spreadsheet I would like to chart for water analysis done by my office. In some of the cells are N/A instead of values. I have tried charting with the "N/A" left in and with a blank cell. Both don't work very well. Could you help me smooth out the line chart? Below is an example; the database is much larger:

Sample Number Ph Cl FL
GP050001 7.50 1.37 N/A
GP050002 6.50 N/A .30
GP050003 7.40 0.05 N/A
GP050004 7.50 0.11 .45

Also, the data categories are substantially different. I would like to group all categories by displaying multiple line charts to help establish relationships (if any). The Ph range is 6.5 - 8; Chlorine is anywhere from trace to 1.4. So, you can see that the data is not within the same range. I am sure there is a way to display the range for Ph on the left "X" vertical column and to display a range for the Cl on the right. Could you help me through that process please? I look forward to it,

JW

Change the N/A to #N/A, which now turns it into an Excel error, not a
random piece of text. In a line chart, Excel treats a bit of text as a
zero, but #N/A as something to be interpolated over. If there are points
on either side, Excel will draw the line from point to point, ignoring
the #N/A value.

With a chart that has multiple series, you can double click on any
series, and on the Axis tab of the Format Series dialog, choose
Secondary. Now you have two different scales at your disposal.

I see you also have a third set of distinct values. Excel's got no
native way to display a third Value axis, but if you're a little
adventurous, you can fake it:

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

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


willisj wrote:
> I have a spreadsheet I would like to chart for water analysis done by my
> office. In some of the cells are N/A instead of values. I have tried
> charting with the "N/A" left in and with a blank cell. Both don't work
> very well. Could you help me smooth out the line chart? Below is an
> example; the database is much larger:
>
> Sample Number Ph Cl FL
> GP050001 7.50 1.37 N/A
> GP050002 6.50 N/A .30
> GP050003 7.40 0.05 N/A
> GP050004 7.50 0.11 .45
>
> Also, the data categories are substantially different. I would like to
> group all categories by displaying multiple line charts to help
> establish relationships (if any). The Ph range is 6.5 - 8; Chlorine is
> anywhere from trace to 1.4. So, you can see that the data is not within
> the same range. I am sure there is a way to display the range for Ph
> on the left "X" vertical column and to display a range for the Cl on
> the right. Could you help me through that process please? I look
> forward to it,
>
> JW
>
>