Chart help please!!!

[Brian Sells]

I have created a macro that will make a chart from a toolbar button I
created. My problem is this, I need the chart to show a moving range and also
the UCL,LCL,and Average lines. The UCL and LCL are calculated with a formula
on my worksheet as well as the average. Here is how I have things set up. I
have a column of data in A1-A10. My formulas are in Col B.
B1=Average(A1:A10)
B2=STDEV(A1:A10)
B3=(b2*3)+B1 {UCL Formula}
B4=B1-(b2*3) {LCL Formula}

I need my chart to show the Moving Range plus lines for these STDEV
calculations. I have an add-in for excel called StatPlus that will make these
charts but it won't let me do it through Excel or through VBA. How could I go
about creating this chart without using the StatPlus add-in? Is it possible
to make 2 seperate charts, one transparent over the other that would still
show all the data I need? I have been through book after book and site after
site searching for this and can't get anywhere. Took me a while just to get
the buttons made and working the way I want. I'm new to VBA but gaining on it
with every keystroke. Any help would be greatly appreciated>
Thanks

 

[Tushar Mehta]

Combine the ideas in two tutorials for a solution that needs no
programming.

Given a range of data named aRng, the following shows how to draw a
horizontal line related to the range and some statistic:

Straight lines
http://www.tushar-mehta.com/excel/charts/straight_lines/index.html

The article uses AVERAGE but you can use UCL and LCL (and anything else
you want).

While the above page uses a statically defined aRng, the following
shows how to make it dynamic (specifically, example 2 shows how to
graph only the last so many values):
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions