dynamic range and series

[cass calculator]

I am doing regression analysis for tree size (independent variable)
and tree age (dependent variable). I want to make a template that
will allow me to chart a dynamic number of trees (data points) and
tree types (series) on a scatterplot. So, I have three columns:
dependent, independent, and tree type.

I want to generate a scatterplot on X and Y axis that has each tree
type as a seperate series. Again, this should work for a dynamic
number of trees (data points) and tree types (series).

This template is meant to be for a given site of trees. I would like
to have the chart on a seperate worksheet. That way, when I add more
sites, I can just copy the chart and the data template, add in the new
data and be done. = )

An amazing bonus feature would also be to be able to merge all the
sites once I am done adding sites. So for a dynamic number of
workseets with a given's sites data, be able to merge all that data
into one worksheet.

I have been trying to figure this out with no luck. I greatly
appreciate anyone that can provide a solution.

Thank You !

Joshua

 

[Don Guillett]

Pls ask in ONE group ONLY

 

[Jon Peltier]

Well, I saw your post here first, and nobody's really answered either yet,
so here goes.

You have three columns of data, and you want to have two sheets for each
item in column 3, one worksheet having the number and size of trees in a
table, and a chart sheet (not a worksheet, if you get the terminology
straight) plotting this data.

Your template should have a worksheet named PivotData for the data (three
columns). Start with the labels (Number of Trees, Tree Size, Tree Type) in
A1:C1, and enter the data in the columns below, and don't skip any rows.
Define a name called "PivotData" to encompass this data: go to Insert menu >
Names > Define, type PivotData in the Name box, and enter this formula in
the RefersTo box:

=Offset(PivotData!$A$1,0,0,COUNTA(PivotData!$A:$A,3)

This creates a dynamic named range that grows as you add data.

Still creating the template here. Create a pivot table based on this range
(Data menu). In step 2, change the cell address to PivotData (the name of
the dynamic range), and in step 3 select New Sheet. Rename the new sheet
Pivot Table. Drag the Tree Size field to the Rows area, the Tree Type field
to the Page area, and the Number of Trees to the Data area. Click the
dropdown next to the Tree Type field name in the Page area, and choose one
tree type. This gives you the stats for all trees. Select a cell in the
table, and click the Chart Wizard button. This creates a chart based on the
pivot table.

To examine another tree type, either select a different tree type from the
dropdown where you selected the first type, or you could copy the Pivot
Table worksheet, then select the new tree type on the copy, then make
another chart. I'd say do the first, because it is dynamic, and you won't be
stuck with lots of extra sheets. Changing the tree type is no more difficult
than selecting a different active sheet. In fact, you can select tree type
from the chart sheet as well.

To show all tree types, drag the Tree Type field button from the Page area
to the Columns area of a pivot table or to the Series area of the chart.
This shows each tree type as a separate series in the chart.

- Jon

 

[Joshua M.]

Well, I saw your post here first, and nobody's really answered either yet,
so here goes.

You have three columns of data, and you want to have two sheets for each
item in column 3, one worksheet having the number and size of trees in a
table, and a chart sheet (not a worksheet, if you get the terminology
straight) plotting this data.

Your template should have a worksheet named PivotData for the data (three
columns). Start with the labels (Number of Trees, Tree Size, Tree Type) in
A1:C1, and enter the data in the columns below, and don't skip any rows.
Define a name called "PivotData" to encompass this data: go to Insert menu >
Names > Define, type PivotData in the Name box, and enter this formula in
the RefersTo box:

=Offset(PivotData!$A$1,0,0,COUNTA(PivotData!$A:$A,3)

This creates a dynamic named range that grows as you add data.

Still creating the template here. Create a pivot table based on this range
(Data menu). In step 2, change the cell address to PivotData (the name of
the dynamic range), and in step 3 select New Sheet. Rename the new sheet
Pivot Table. Drag the Tree Size field to the Rows area, the Tree Type field
to the Page area, and the Number of Trees to the Data area. Click the
dropdown next to the Tree Type field name in the Page area, and choose one
tree type. This gives you the stats for all trees. Select a cell in the
table, and click the Chart Wizard button. This creates a chart based on the
pivot table.

To examine another tree type, either select a different tree type from the
dropdown where you selected the first type, or you could copy the Pivot
Table worksheet, then select the new tree type on the copy, then make
another chart. I'd say do the first, because it is dynamic, and you won't be
stuck with lots of extra sheets. Changing the tree type is no more difficult
than selecting a different active sheet. In fact, you can select tree type
from the chart sheet as well.

To show all tree types, drag the Tree Type field button from the Page area
to the Columns area of a pivot table or to the Series area of the chart.
This shows each tree type as a separate series in the chart.

- Jon

Thanks for your response Jon - it is well appreciated. While your
response does accomplish part of what I was trying to do, it does not
allow me to do it in a scatterplot. You have helped me figure out how
to create a dynamic range, which does indeed help a great deal.

Unfortunately, I believe you were under the impression I was trying to
make a frequency (distribution) chart. In that case, your solution
would have been perfect. However, I am making a scatterplot for the
purposes of determining how tree size (independent variable),
determines tree age (dependent variable). This is a regression
analysis. My variables are tree size, tree age and tree type. I am
trying to make a scatterplot that plots the dependent variable on the
Y axis and the independent variable on the X. Each tree type is a
different series, and the range of the series are the corresponding
size and age data for only those particular trees. Therefore, the dot
on the scatterplot would be a different color depending on what tree
type it is. The only way ive been able to create a scatterplot with
different series is by manually adding the series within the chart
wizard. If you try to enter a data range to allow excel to determine
the series, it does not accomplish what I am trying to do, regardless
of if you select "series in rows" or "series in columns".

Does that make sense? Hopefully you have a few more tricks up your
sleeve !

Thanks,

Joshua

 

[Jon Peltier]

First, isn't tree age the independent variable?

Use this technique to split out the data by tree type:

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

Or use tree type as the left most variable in the rows area of the pivot
table, and make a regular XY chart from the data:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=553

- Jon