Charting data points and show a target range on the same chart.

[Minireefkeeper]

Can anyone offer advise on how to make a chart of data points (line) with in
a particular range of target values? Speciffically, I am try to chart the pH
values of my fish tank. I know how to plot the data points on a line graph,
but I'd like to add a visual "highlighted" target range section. Ie, my data
records the pH for three days (8.2, 8.4, 8.5). pH on the y-axis and the date
on the x-axis. The acceptable range for the pH values for a saltwater tank
is 8.1 - 8.5 How can I indicate this target range within the chart? Once I
learn how to do this I can apply it to other tests (nitrates etc..)
i'd sure appreciate some help...

 

[Barb R.]

If it were me, I'd plot this using a control chart. Take a look at Jon
Peltier's example here:

http://www.peltiertech.com/Excel/Charts/RunChtLines.html

 

[John Mansfield]

There are several ways to do this. The example illustrates an approach that
uses a combined line - stacked column chart:

To add the highlighted range and assuming you acceptable range is 8.1 - 8.5,
set your data up like below. The first column is the date or time of your ph
reading. The second column is you ph reading. The third and fourth columns
will be used to set up highlighted range. The third column is the bottom of
the range you want to highlight. The fourth column is the top of the range
you want to highlight.

4/16/2004 8.3 8.1 0.4
4/17/2004 8.6 8.1 0.4
4/18/2004 8.4 8.1 0.4
4/19/2004 8.2 8.1 0.4
4/20/2004 8.4 8.1 0.4
4/21/2004 8.6 8.1 0.4

Assuming the data set above spans the range A16, create a Stacked Column
chart based on the entire data set.

Next, click once on the series in the stacked column chart that contains
your actual data (it's the bottom column within each stacked column).
Right-click on your mouse and select Chart Type -> Line ->OK.

Click once on the bottom set of columns. Right-click on your mouse and
select Selected Object. In the Format Data Series dialog box, go to the
Patterns tab. Set the Border = None and the Area = None.

Click once on the middle set of columns (they should be the only columns
appearing in the chart now. Right-click on your mouse and select Selected
Object. In the Format Data Series dialog box, go to the Patterns tab. Set
the Border = None. Finally, still within the Format Data Series dialog box,
go to the options tab and set the gap width to zero.

 

[John Mansfield]

A second, and easier method is to use a stacked area chart. Assuming the
same data set,

4/16/2004 8.3 8.1 0.4
4/17/2004 8.6 8.1 0.4
4/18/2004 8.4 8.1 0.4
4/19/2004 8.2 8.1 0.4
4/20/2004 8.4 8.1 0.4
4/21/2004 8.6 8.1 0.4

Select the range. Go to the Chart Wizard and, under the standard types,
select -> Area - > Stacked Area. Go though the rest of the Chart Wizard
prompts until your appears on you page.

Click once on the series at the bottom of the chart. Right-click on you
mouse. Select Chart Type -> Line -> OK.

Click once on the series at the bottom of the chart. Right-click on you
mouse. Select Selected Object. In the Format Data Series -> Patterns Tab,
set the border = None and the Patterns = None.

 

[MiniReefKeeper]

That was EXCEPTIONALLY helpful! Thank you. I have numerous applications I
can apply this too. Thanks so much,
MiniReefKeeper

 

[John Mansfield]

You're welcome. Additionally, I've added an explanation to my site for
documentation.

http://www.pdbook.com/index.php/excel/horizontal_color_bands_stacked_area_chart/

 

[hayleigh]

Wit U Talkin Aboot Ya BigFat Wamk