Thread: Chart Point Analysis

Does anyone know of a method for analysing points on a line chart that I have
created?

I have a chart with 30point on its, and a line plotted along the middle
which is the average of the 30 readings.

I have to use the following rules to check the chart, looking for the
following conditions.

1. 7 points or more in a row on one side (above or below) the average
2. 7 points in a row that are constantly decreasing no matter where they
fall in
relation to the average.
3. 7 points in a row that are constantly increasing no matter where they
fall in
relation to the average.
4. Do 2/3rds of the points lie within the middle 1/3rd of the graph

I'm not sure if any of this can be re-created automatically maybe using VBA?
But if anyone has any suggestions as to how I can automate this analysis, so
that I can give the Workbook to unskilled operators and let it tell them when
one of the rules is broken, then your suggestions will be very welcome

Thanks in advance

Ian

It looks like you are using Shewhart rules for this. I've never seen Excel
used to determine if any of the tests fail. I've used other software
packages for it. I'm sure someone else can answer your question, I'm
wondering if you'd want to see if the rule failed for the last n points, or
do you want the chart to display every time the rule fails.

Jon Peltier's Dynamic control chart may help you.

http://www.peltiertech.com/Excel/Cha...s.html#CtrlCht

1. 7 points or more in a row on one side (above or below) the average
> 2. 7 points in a row that are constantly decreasing no matter where they
> fall in
> relation to the average.
> 3. 7 points in a row that are constantly increasing no matter where they
> fall in
> relation to the average.
> 4. Do 2/3rds of the points lie within the middle 1/3rd of the graph

"Neil" wrote:

> Does anyone know of a method for analysing points on a line chart that I have
> created?
>
> I have a chart with 30point on its, and a line plotted along the middle
> which is the average of the 30 readings.
>
> I have to use the following rules to check the chart, looking for the
> following conditions.
>
> 1. 7 points or more in a row on one side (above or below) the average
> 2. 7 points in a row that are constantly decreasing no matter where they
> fall in
> relation to the average.
> 3. 7 points in a row that are constantly increasing no matter where they
> fall in
> relation to the average.
> 4. Do 2/3rds of the points lie within the middle 1/3rd of the graph
>
> I'm not sure if any of this can be re-created automatically maybe using VBA?
> But if anyone has any suggestions as to how I can automate this analysis, so
> that I can give the Workbook to unskilled operators and let it tell them when
> one of the rules is broken, then your suggestions will be very welcome
>
> Thanks in advance
>
> Ian
>
>
>
>

Yes, they are indeed Shewhart rules, and I need to make an idiot proof
control chart for use on our shop floor.

I would want it to test the entire chart as soon as a new set of data is
entered, and as all the data is already held in various cells I'm beginning
to wonder if I can run the test in VBA code on the data retrieved from the
cells, rather than on the chart.

On another point, you mention that you use other programs for doing this, do
you have one that will do what I want? If so which is it, as it might be
better for me to purchase that than spend too long working on this problem.

Thanks

Neil

"Barb R." wrote:

> It looks like you are using Shewhart rules for this. I've never seen Excel
> used to determine if any of the tests fail. I've used other software
> packages for it. I'm sure someone else can answer your question, I'm
> wondering if you'd want to see if the rule failed for the last n points, or
> do you want the chart to display every time the rule fails.
>
> Jon Peltier's Dynamic control chart may help you.
>
> http://www.peltiertech.com/Excel/Cha...s.html#CtrlCht
>
> 1. 7 points or more in a row on one side (above or below) the average
> > 2. 7 points in a row that are constantly decreasing no matter where they
> > fall in
> > relation to the average.
> > 3. 7 points in a row that are constantly increasing no matter where they
> > fall in
> > relation to the average.
> > 4. Do 2/3rds of the points lie within the middle 1/3rd of the graph
>
> "Neil" wrote:
>
> > Does anyone know of a method for analysing points on a line chart that I have
> > created?
> >
> > I have a chart with 30point on its, and a line plotted along the middle
> > which is the average of the 30 readings.
> >
> > I have to use the following rules to check the chart, looking for the
> > following conditions.
> >
> > 1. 7 points or more in a row on one side (above or below) the average
> > 2. 7 points in a row that are constantly decreasing no matter where they
> > fall in
> > relation to the average.
> > 3. 7 points in a row that are constantly increasing no matter where they
> > fall in
> > relation to the average.
> > 4. Do 2/3rds of the points lie within the middle 1/3rd of the graph
> >
> > I'm not sure if any of this can be re-created automatically maybe using VBA?
> > But if anyone has any suggestions as to how I can automate this analysis, so
> > that I can give the Workbook to unskilled operators and let it tell them when
> > one of the rules is broken, then your suggestions will be very welcome
> >
> > Thanks in advance
> >
> > Ian
> >
> >
> >
> >

Good luck making control charts idiot proof. I remember a time when someone
"FORGOT" to run a 3 hour furnace run and when they "measured" the control
wafers, all measured EXACTLY 400 angstroms, which coincidentally was the
target for the process. They entered the data into the control charting
software and everything was in control and off the product went. Only to
fail later.

I've used "home grown" solutions created by our internal IT department.
One was SAS/AF based and one was PROMIS based. Have you checked with ASQ
(asq.org) or ISIXSIGMA (isixsigma.com) to see what they might suggest?

"Neil" wrote:

> Yes, they are indeed Shewhart rules, and I need to make an idiot proof
> control chart for use on our shop floor.
>
> I would want it to test the entire chart as soon as a new set of data is
> entered, and as all the data is already held in various cells I'm beginning
> to wonder if I can run the test in VBA code on the data retrieved from the
> cells, rather than on the chart.
>
> On another point, you mention that you use other programs for doing this, do
> you have one that will do what I want? If so which is it, as it might be
> better for me to purchase that than spend too long working on this problem.
>
> Thanks
>
> Neil
>
> "Barb R." wrote:
>
> > It looks like you are using Shewhart rules for this. I've never seen Excel
> > used to determine if any of the tests fail. I've used other software
> > packages for it. I'm sure someone else can answer your question, I'm
> > wondering if you'd want to see if the rule failed for the last n points, or
> > do you want the chart to display every time the rule fails.
> >
> > Jon Peltier's Dynamic control chart may help you.
> >
> > http://www.peltiertech.com/Excel/Cha...s.html#CtrlCht
> >
> > 1. 7 points or more in a row on one side (above or below) the average
> > > 2. 7 points in a row that are constantly decreasing no matter where they
> > > fall in
> > > relation to the average.
> > > 3. 7 points in a row that are constantly increasing no matter where they
> > > fall in
> > > relation to the average.
> > > 4. Do 2/3rds of the points lie within the middle 1/3rd of the graph
> >
> > "Neil" wrote:
> >
> > > Does anyone know of a method for analysing points on a line chart that I have
> > > created?
> > >
> > > I have a chart with 30point on its, and a line plotted along the middle
> > > which is the average of the 30 readings.
> > >
> > > I have to use the following rules to check the chart, looking for the
> > > following conditions.
> > >
> > > 1. 7 points or more in a row on one side (above or below) the average
> > > 2. 7 points in a row that are constantly decreasing no matter where they
> > > fall in
> > > relation to the average.
> > > 3. 7 points in a row that are constantly increasing no matter where they
> > > fall in
> > > relation to the average.
> > > 4. Do 2/3rds of the points lie within the middle 1/3rd of the graph
> > >
> > > I'm not sure if any of this can be re-created automatically maybe using VBA?
> > > But if anyone has any suggestions as to how I can automate this analysis, so
> > > that I can give the Workbook to unskilled operators and let it tell them when
> > > one of the rules is broken, then your suggestions will be very welcome
> > >
> > > Thanks in advance
> > >
> > > Ian
> > >
> > >
> > >
> > >

Neil -

These rules can be built into Excel formulas or VBA routines; I did it some time ago
using simplified versions of these rules. The rules are tested on the worksheet
data, not on the charts, although the results can be displayed in the charts.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Neil wrote:

> Yes, they are indeed Shewhart rules, and I need to make an idiot proof
> control chart for use on our shop floor.
>
> I would want it to test the entire chart as soon as a new set of data is
> entered, and as all the data is already held in various cells I'm beginning
> to wonder if I can run the test in VBA code on the data retrieved from the
> cells, rather than on the chart.
>
> On another point, you mention that you use other programs for doing this, do
> you have one that will do what I want? If so which is it, as it might be
> better for me to purchase that than spend too long working on this problem.
>
> Thanks
>
> Neil
>
> "Barb R." wrote:
>
>
>>It looks like you are using Shewhart rules for this. I've never seen Excel
>>used to determine if any of the tests fail. I've used other software
>>packages for it. I'm sure someone else can answer your question, I'm
>>wondering if you'd want to see if the rule failed for the last n points, or
>>do you want the chart to display every time the rule fails.
>>
>>Jon Peltier's Dynamic control chart may help you.
>>
>>http://www.peltiertech.com/Excel/Cha...s.html#CtrlCht
>>
>> 1. 7 points or more in a row on one side (above or below) the average
>>
>>>2. 7 points in a row that are constantly decreasing no matter where they
>>>fall in
>>>relation to the average.
>>>3. 7 points in a row that are constantly increasing no matter where they
>>>fall in
>>>relation to the average.
>>>4. Do 2/3rds of the points lie within the middle 1/3rd of the graph
>>
>>"Neil" wrote:
>>
>>
>>>Does anyone know of a method for analysing points on a line chart that I have
>>>created?
>>>
>>>I have a chart with 30point on its, and a line plotted along the middle
>>>which is the average of the 30 readings.
>>>
>>>I have to use the following rules to check the chart, looking for the
>>>following conditions.
>>>
>>>1. 7 points or more in a row on one side (above or below) the average
>>>2. 7 points in a row that are constantly decreasing no matter where they
>>>fall in
>>>relation to the average.
>>>3. 7 points in a row that are constantly increasing no matter where they
>>>fall in
>>>relation to the average.
>>>4. Do 2/3rds of the points lie within the middle 1/3rd of the graph
>>>
>>>I'm not sure if any of this can be re-created automatically maybe using VBA?
>>>But if anyone has any suggestions as to how I can automate this analysis, so
>>>that I can give the Workbook to unskilled operators and let it tell them when
>>>one of the rules is broken, then your suggestions will be very welcome
>>>
>>>Thanks in advance
>>>
>>>Ian
>>>
>>>
>>>
>>>