Can't create dynamic charts

[Guest]

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.

 

[Guest]

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/Pub0009/LPMArticle.asp?ID=518

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

 

[Guest]

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

 

[Jon Peltier]

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/
_______

 

[Guest]

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

 

[Jon Peltier]

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/
_______

 

[Guest]

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

 

[Jon Peltier]

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/
_______