Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic range and series
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic range and series
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic range and series
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "cass calculator" wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic range and series
On Feb 25, 10:51 am, "Jon Peltier"
wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutionshttp://PeltierTech.com _______ "cass calculator" wrote in message ups.com... 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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic range and series
First, isn't tree age the independent variable?
Use this technique to split out the data by tree type: http://peltiertech.com/Excel/Charts/...nalChart1.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/P...cle.asp?ID=553 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Joshua M." wrote in message ps.com... On Feb 25, 10:51 am, "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutionshttp://PeltierTech.com _______ "cass calculator" wrote in message ups.com... 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Series Range | Charts and Charting in Excel | |||
dynamic data range and series | Charts and Charting in Excel | |||
dynamic range and series | Excel Worksheet Functions | |||
Dynamic series range from AutoFilter | Charts and Charting in Excel | |||
need help updating chart (series in dynamic range) | Charts and Charting in Excel |