Automate Statistical Process Anaylsis

[hchurch]

I am trying to generate formulas to perform automatic detection of special cause variation on a control chart. I have attached a sample of the workbook that I am using. Sheet1 contains the data and sheet2 contains the charts. I am trying to create formulas in columns G - I to detect occurences based off of 3 rules.

Rule 1:
Any single data point falls outside the 3σ limit from the centerline

Rule 2:
Two out of three consecutive points fall beyond the 2σ limit (in zone A or beyond), on the same side of the centerline

Rule 3:
Four out of five consecutive points fall beyond the 1σ limit (in zone B or beyond), on the same side of the centerline

The formulas I have are partially working, just not giving me the desired results. Column I Rows 20-25 should all have the value from Column B, as these all fall under rule 3.

[shg]

I think that, for example, in cols H and I, you need to consider a range of 9 cells (four above and below a given value). A clever person would do this with formulas; I used a UDF.

I hosed your threshold lines, sorry.

[mycon73]

Hi,

I'm trying to something similar to this, except I'm collecting data for reverse engineering, then seeking a way to nominalize the data & represent some confidence level to validate the nominal value.

Did you get your answer hchurch?

[shg]

If you have a question, mycon, please start your own thread.

Thanks.

[hchurch]

Thanks for the response shg. I am still working with our IT group to be able to access the file download. I want to see what you did. I tried using a formula that does what you recommended but still didn't get the desired output.

[selopezr]

Hi hchurch,
I think Column I, rows 20-22 doesn't fall under rule 3, as you defined it in your formula:

• - row 20: Only rolls 15 and 19 (2 data points) are lower than $K$7
  - row 21: Only rolls 19 and 20 (2 data points) are lower than $K$7
  - row 22: Rolls 19, 20 and 21 (3 data points) are lower than $K$7
  - row 23: Rolls 19, 20, 21 and 22 (4 data points) are lower than $K$7, so it's basis weight is displayed in column I.

and so on.

Can you comment why do you need rows 20 to 22 to be included as points falling in the lower b zone?

selopezr.

[hchurch]

Here is another workbook. The sheet WantedOutput shows what the results should be (I analyzed them and just moved the numbers manually).

[selopezr]

Hi hchurch,
Since you are trying to run a SPC system, you should base your analysis in the data entered previous to the current point. I've seen in your formulas that you select the previous and the forth 2 or 4 data points depending on the rule you are using. In production you can't predict the values of the subsequent data. Otherwise, you'll be applying the control criteria (the rules) to a chart of data gathered in the past, which is not the aim of SPC. Further more, to use previous and subsequent data points doesn't make sense if you define a rule for the data above and another rule for the data below the defined values.
I reviewed, graphed and analyzed your new workbook and I think your wanted output still doesn't match the criteria.
Please confirm if you still want to highlight all the data falling within a specific rule, considering the previous and the forth points.
I would like to help with this but I need to understand exactly what you want.
All the best.
selopezr

[hchurch]

Thanks everyone for the advice. I have developed some formulas that seem to work. I am in the process of doing some testing and if everything tests out "OK" then I will post the workbook here for everyone to have access to.