Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can't create dynamic charts
OK, here is my problem....I am trying to create a dynamic x-bar chart and a
dynamic moving range chart. I am using the StatPlus add-in and can't get this to work. I have been to probably 30 websites about dynamic charting and have found nothing that applies to either of these charts. I think part of the problem is that I have to create dynamic range names for the data. However, when I try all the different methods of doing this that I've found I am getting a #value error in the cell. This is basically what I want in my chart( of course this is an example, you wouldnt believe the amount of data in the actual sheet.) I need an x-bar and a moving range chart that will allow me to update the data AND add data and have the chart update automatically. Here is a sample of my data A B 1 Vendor Performance 2 1 36 3 2 33 4 3 45 5 4 39 6 5 38 7 6 40 8 7 46 9 8 34 10 9 32 This data changes constantly and I really need dynamic xbar and moving range charts for this so I dont have to create a new chart every time the numbers change or numbers are added. I have found several pages on creating dynamic charts but they mostly apply to bar charts. I know that it's possible to create these charts dynamically because I have seen them. Any ideas? Thanks in advance for any help. |
#2
|
|||
|
|||
Brian,
Perhaps these references that describe techniques of dynamic charting can help - the processes for creating the named ranges is described in detail: http://pubs.logicalexpressions.com/P...cle.asp?ID=518 http://pubs.logicalexpressions.com/P...cle.asp?ID=246 ---- Regards, John Mansfield http://www.pdbook.com "Brian Sells" wrote: OK, here is my problem....I am trying to create a dynamic x-bar chart and a dynamic moving range chart. I am using the StatPlus add-in and can't get this to work. I have been to probably 30 websites about dynamic charting and have found nothing that applies to either of these charts. I think part of the problem is that I have to create dynamic range names for the data. However, when I try all the different methods of doing this that I've found I am getting a #value error in the cell. This is basically what I want in my chart( of course this is an example, you wouldnt believe the amount of data in the actual sheet.) I need an x-bar and a moving range chart that will allow me to update the data AND add data and have the chart update automatically. Here is a sample of my data A B 1 Vendor Performance 2 1 36 3 2 33 4 3 45 5 4 39 6 5 38 7 6 40 8 7 46 9 8 34 10 9 32 This data changes constantly and I really need dynamic xbar and moving range charts for this so I dont have to create a new chart every time the numbers change or numbers are added. I have found several pages on creating dynamic charts but they mostly apply to bar charts. I know that it's possible to create these charts dynamically because I have seen them. Any ideas? Thanks in advance for any help. |
#3
|
|||
|
|||
John,
Thanks a bunch for that, it actually worked for making my charts dynamic. Now I have another problem. I have the charts operating dynamically but now I've lost the UCL,LCL, and Center Lines on the chart. Any ideas on that? Once again thanks a lot. Your website had a lot of stuff I can use. Regards, Brian |
#4
|
|||
|
|||
Brian -
I don't know how you had those lines before or why they disappeared. This web page describes how to add lines to a run chart. For the example I used simply mean and mean ± SD, but you can just as easily use UCL and LCL. http://peltiertech.com/Excel/Charts/RunChtLines.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Brian Sells wrote: John, Thanks a bunch for that, it actually worked for making my charts dynamic. Now I have another problem. I have the charts operating dynamically but now I've lost the UCL,LCL, and Center Lines on the chart. Any ideas on that? Once again thanks a lot. Your website had a lot of stuff I can use. Regards, Brian |
#5
|
|||
|
|||
Jon,
Thanks for that page... I am going to try the stuff in it and see if it is what I need. The lines I'm refering to are the Upper Control Limit(UCL), Lower Control Limit(LCU) and the center line is basically an average line. These lines are automatically put in the chart by the StatPlus add-in I have for excel. These are process control charts and when you create them there is no option for creating them dynamically. With John's page I was able to get the xbar and moving range charts to become dynamic but I lost the lines. They are a critical part of the chart and the values that are computed for them is the precise reason I need them in there. The UCL and LCL are the limits, positive or negative, that I need for the spreadsheet. Basically it is all statistical monitoring but I need them for what I am creating. I will definitely try the methods from your site before I go any farther and let you know if it does what I need it to do. Thanks for the assistance. Regards, Brian Sells |
#6
|
|||
|
|||
Hi Brian -
I am familiar with the terms UCL and LCL. Do you know the formula StatPlus uses to calculate these control limits? For many purposes, ± 3 SD is used. For other applications, for example, design allowables for aerospace materials, the relationship is more complex, based on confidence limits which are close to ± 3 SD for a typical number of observations, but may be wider or narrower depending on the actual number of observations in the data base from which they are derived. Blah blah. The point is, if you know the limits, you can add the lines. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Brian Sells wrote: Jon, Thanks for that page... I am going to try the stuff in it and see if it is what I need. The lines I'm refering to are the Upper Control Limit(UCL), Lower Control Limit(LCU) and the center line is basically an average line. These lines are automatically put in the chart by the StatPlus add-in I have for excel. These are process control charts and when you create them there is no option for creating them dynamically. With John's page I was able to get the xbar and moving range charts to become dynamic but I lost the lines. They are a critical part of the chart and the values that are computed for them is the precise reason I need them in there. The UCL and LCL are the limits, positive or negative, that I need for the spreadsheet. Basically it is all statistical monitoring but I need them for what I am creating. I will definitely try the methods from your site before I go any farther and let you know if it does what I need it to do. Thanks for the assistance. Regards, Brian Sells |
#7
|
|||
|
|||
Jon,
I don't know what the formulas are that StatPlus uses. It's just a wizard that you use to create the charts. Problem is it doesn't allow you to make the xbar or moving range charts dynamic. I was able to get around this by creating a dynamic range for the data rather than a dynamic chart. If you look at the chart properties it still says it's a static chart but it actually works dynamically.The methods described in your page for manually creating the lines didn't work. I wasnt able to paste them in and when typed in manually I got a formula error. I don't really know the limits which is why I'm creating the charts. I had another idea today that would work for what I want to do, I believe anyway but I am not sure to go about doing it. Is there a way I could create a button in the worksheet that would recreate the chart any time it was pushed. Tried making a macro for this but it didn't work. I wouldn't need the chart to be dynamic if I could create something like this that would automatically overwrite the previous chart. I just don't want to have to go through the whole process of creating a new chart everytime my data changes. Any thoughts on this? I don't know any visual basic but can probably learn it pretty quick to accomplish what I need. Thanks in advance for any more help you can offer up. Regards, Brian |
#8
|
|||
|
|||
Brian -
I don't know how the StatPlus add-in works. It seems strange that an Excel programmer wouldn't allow dynamic ranges for a chart like this, since you're always adding data to a run chart, by definition. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Brian Sells wrote: Jon, I don't know what the formulas are that StatPlus uses. It's just a wizard that you use to create the charts. Problem is it doesn't allow you to make the xbar or moving range charts dynamic. I was able to get around this by creating a dynamic range for the data rather than a dynamic chart. If you look at the chart properties it still says it's a static chart but it actually works dynamically.The methods described in your page for manually creating the lines didn't work. I wasnt able to paste them in and when typed in manually I got a formula error. I don't really know the limits which is why I'm creating the charts. I had another idea today that would work for what I want to do, I believe anyway but I am not sure to go about doing it. Is there a way I could create a button in the worksheet that would recreate the chart any time it was pushed. Tried making a macro for this but it didn't work. I wouldn't need the chart to be dynamic if I could create something like this that would automatically overwrite the previous chart. I just don't want to have to go through the whole process of creating a new chart everytime my data changes. Any thoughts on this? I don't know any visual basic but can probably learn it pretty quick to accomplish what I need. Thanks in advance for any more help you can offer up. Regards, Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Charts | Excel Discussion (Misc queries) | |||
how to create a dynamic descending list of months & years | Excel Discussion (Misc queries) | |||
How do I create individual pie charts for 99 different employees? | Charts and Charting in Excel | |||
Can I post dynamic line charts by swapping out .cvs files??? | Charts and Charting in Excel | |||
Can I create a dynamic email address in Excels hyperlink? | Excel Worksheet Functions |