G

#### Graeme

After some thought I have decided to go ahead and post this question, because I feel it is sufficiently different from others to be ... different. Besides, I want to use Excel to solve this, not rulers and pencils.

(Fair Notice: I have a long-winded discussion of the problem.)

“

But wait, I say – maybe I’m talking about a special application – an application where a trend is

Standard Shewhart statistical process control (SPC) charts are designed to monitor a process which produces a large number of measurements of the same "identical" feature over a relatively short period of time. Ideally the process holds a constant level. Random variation is easily visible, and that is good. The chart also gives visibility to variation that may not be random, such as points above or below the control limits, or an upward or downward trend. These things are considered bad.

SPC charts are also used in

The bigger problems come when trying to use SPC chart methods to monitor the variation of

Consider the following data set:

When plotted, this series has an upward trend of about +5.4 x 10-9 per day or about +0.000 002 per year. From the regression line, I can be fairly confident in giving the device an assigned value of 1.000 037, and I can be fairly confident that in May 2006 the value will be near 1.000 039. My problem is that I don’t know (yet) how to quantify that confidence – the uncertainty – and without being able to do that these values are meaningless.

If the line was horizontal (slope = 0) then computing a value for the uncertainty of the device would be trivial. (Compute the standard deviation and multiply by an appropriate value of Student’s

How do I get the equivalent result when the center line is a trend?

(By the way, the device is a 1 Megohm standard resistor. But, it doesn’t really matter what it is – it could just as easily be the 1 Volt output of a DC volt standard, or a 1 Ohm standard resistor, or any of a number of other things. Also, precision of one part in 106 is fine for this application.)

TIA,

(Fair Notice: I have a long-winded discussion of the problem.)

**Question – how are control limits for an SPC chart calculated when the center line is a trend?**“

*What?*” you say?*Heresy!*A trend is a “very bad thing” and a sure sign that things are out of control! Get rid of that nasty trend and all will be well in the world!But wait, I say – maybe I’m talking about a special application – an application where a trend is

*expected*,*normal*, and*you can’t do anything to control it or get rid of it*. Let me give a bit of background before re-stating the question.Standard Shewhart statistical process control (SPC) charts are designed to monitor a process which produces a large number of measurements of the same "identical" feature over a relatively short period of time. Ideally the process holds a constant level. Random variation is easily visible, and that is good. The chart also gives visibility to variation that may not be random, such as points above or below the control limits, or an upward or downward trend. These things are considered bad.

SPC charts are also used in

**calibration labs**to monitor measurement processes and measurement standards.**Processes**are typically monitored by use of a check standard – a device that is similar to the workload and measured at the same time as normal work. Recording and charting the values is similar to production SPC charts, except that there is much__less__data. Instead of producing hundreds of parts (measurements) per hour, the lab might generate 200 measurements a year – or only 20, depending on the workload. X and mR charts are much more common than X-bar and R charts. Still, things work about the same as the usual type.The bigger problems come when trying to use SPC chart methods to monitor the variation of

**measurement standards**. First, there is a data problem: if a standard is calibrated every 12 months, it naturally takes five years to get five measurement values. (*In the calibration business, patience really is a virtue.*) Calibrating more frequently than the usual interval will collect data more quickly, but that costs a lot of money and time. So we usually live with it. A bigger problem is that**some things change in value over time**– they drift. This is known and expected. Examples include shrinkage of gage blocks, drift of standard resistor values, change in the output of a DC volt standard, and many more examples. The drift is a natural phenomenon that can be compensated for but not controlled or adjusted. This means that values plotted on a graph**show a trend. On a standard SPC chart a trend may be a bad thing but for many measurement standards it’s just the way they are. The trend can be determined, often with linear regression, so I have a rate and direction of change over time. That can be used to determine an “assigned value” for right now, and to predict a value at times in the near future (up to one calibration interval). If control limits or – even better, 95% confidence limits – could be plotted around that trend, they would give an estimate of the standard deviation which is very useful in computing measurement uncertainty.**__will__**So the question before the group is this**– how does one calculate control limits or confidence limits when the central line is a trend? I am looking for a method using Microsoft**Excel**,*not*a paper printout, ruler and pencil. Besides, I think confidence limits for this will be curves instead of straight lines.Consider the following data set:

Code:

```
[FONT=Courier New][SIZE=3]Date (X) Value (Y)[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]1996-05-01 1.000 017[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]1997-05-13 1.000 024 [/SIZE][/FONT]
[FONT=Courier New][SIZE=3]1998-06-05 1.000 022 [/SIZE][/FONT]
[FONT=Courier New][SIZE=3]1999-07-07 1.000 028 [/SIZE][/FONT]
[FONT=Courier New][SIZE=3]2000-08-02 1.000 024 [/SIZE][/FONT]
[FONT=Courier New][SIZE=3]2001-08-28 1.000 031 [/SIZE][/FONT]
[FONT=Courier New][SIZE=3]2002-09-24 1.000 036 [/SIZE][/FONT]
[FONT=Courier New][SIZE=3]2003-10-22 1.000 032 [/SIZE][/FONT]
[FONT=Courier New][SIZE=3]2004-05-05 1.000 035 [/SIZE][/FONT]
[FONT=Courier New][SIZE=3]2005-05-11 1.000 036[/SIZE][/FONT]
```

If the line was horizontal (slope = 0) then computing a value for the uncertainty of the device would be trivial. (Compute the standard deviation and multiply by an appropriate value of Student’s

*t*for 95% confidence limits.) But the line has a non-zero slope … so,How do I get the equivalent result when the center line is a trend?

(By the way, the device is a 1 Megohm standard resistor. But, it doesn’t really matter what it is – it could just as easily be the 1 Volt output of a DC volt standard, or a 1 Ohm standard resistor, or any of a number of other things. Also, precision of one part in 106 is fine for this application.)

TIA,

Last edited by a moderator: