-1

The following graph represents my dataset, it is change in strain (y axis) and change in temperature (x axis)

As you can see it is a linear relationship, I am wanting to create upper and lower bound thresholds in a robust statistical manner. Below are some attempts.

enter image description here

enter image description here

These are both based on 99% confidence levels but as you can see there are data points which lie well and truly outside this range, this is concerning as this means the upper and lower bounds of this linear equation are just too small, due to the majority of the data set lying well within the curves. I am developing an alarm system which will show "fail" whenever the strain readings jump outside these linear slopes. What I am asking is if there is any other way of statistically producing upper and lower bound curves outside of normal distribution confidence levels, as my methods will sound an alarm all to often!

Thomas Ruddell
  • 21
  • 1
  • 6

1 Answers1

0

In Excel you can calculate the upper and lower curve as follows;

  1. Use the LINEST(y_data,x_data,TRUE,TRUE) function to fit your data (with stats=TRUE to return regression statistics

  2. LINEST will return a 5 row by 2 column table, which contains:
    m b
    SEm SEb
    R2 SEy
    F DOF
    SSreg SSres

  3. The curves are calculated with the equation:

    =y0 +/- TINV(alpha, DOF) * SQRT(SEy^2/N + SEm^2*(x0-xavg)^2)

In the above equation:
x0 is any x-value
xavg is the average of your xdata (calculate with AVG function)
y0 = m*x0 + b
N is the number of data points
DOF is the degrees of freedom (LINEST returns this)
SEy is the standard error returned by LINEST
SEm is the standard error of the slope (returned by LINEST)
alpha is 1 minus desired confidence level TINV is Excel's function for calculating t-statistics +/- do this calculation twice for each x0: once with + to get the upper curve, once with - to get the lower curve

Plot the two curves you generate vs x.

Also, check out the Analysis Toolpak --- it may provide a more automated way to do confidence intervals.

Hope that helps

xidgel
  • 3,085
  • 2
  • 13
  • 22