Blank cells and Charting multiple data points

[willisj]

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

 

[Jon Peltier]

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/
_______