Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Minireefkeeper
 
Posts: n/a
Default Charting data points and show a target range on the same chart.

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 sal****er 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...

  #2   Report Post  
Barb R.
 
Posts: n/a
Default

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

http://www.peltiertech.com/Excel/Cha...nChtLines.html

"Minireefkeeper" wrote:

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 sal****er 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...

  #3   Report Post  
John Mansfield
 
Posts: n/a
Default

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 A1:D6, 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.

----
Regards,
John Mansfield
http://www.pdbook.com

"Minireefkeeper" wrote:

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 sal****er 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...

  #4   Report Post  
John Mansfield
 
Posts: n/a
Default

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.

----
Regards,
John Mansfield
http://www.pdbook.com



"John Mansfield" wrote:

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 A1:D6, 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.

----
Regards,
John Mansfield
http://www.pdbook.com

"Minireefkeeper" wrote:

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 sal****er 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...

  #5   Report Post  
MiniReefKeeper
 
Posts: n/a
Default

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

"John Mansfield" wrote:

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.

----
Regards,
John Mansfield
http://www.pdbook.com



"John Mansfield" wrote:

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 A1:D6, 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.

----
Regards,
John Mansfield
http://www.pdbook.com

"Minireefkeeper" wrote:

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 sal****er 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...



  #6   Report Post  
John Mansfield
 
Posts: n/a
Default

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

http://www.pdbook.com/index.php/exce...ed_area_chart/

----
Regards,
John Mansfield
http://www.pdbook.com

"MiniReefKeeper" wrote:

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

"John Mansfield" wrote:

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.

----
Regards,
John Mansfield
http://www.pdbook.com



"John Mansfield" wrote:

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 A1:D6, 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.

----
Regards,
John Mansfield
http://www.pdbook.com

"Minireefkeeper" wrote:

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 sal****er 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...

  #7   Report Post  
Posted to microsoft.public.excel.charting
hayleigh
 
Posts: n/a
Default Charting data points and show a target range on the same chart


Wit U Talkin Aboot Ya BigFat Wamk


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 11:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"