The goal: Kappa-Sigma clipping in Excel
I want to implement a selective linear regression formula in Excel (avoiding VBA). I have a data sample on which I need to apply a linear regression. But my data has some structural outliers. I want to filter them out using a kappa-sigma clipping. Basically this just does a linear regression, calculates the error per datapoint and if a datapoint had a large error (some constant [kappa] times the standard deviation of all errors [sigma]), it will be rejected from the next iteration. Then the linear regession is applied again on the clipped dataset.
What I have so far
I have an Excel sheet with dummy data (y = 3 * x + some error). Three datapoints (x = 6, x = 10 and x = 16) have a structural (measurement) error. So I fit the data (A25:C29
), calculate the error (column D) and check if the error is bigger than kappa (1) times the standard deviation of the error's (B31
). The results are in column E. You can see that the three datapoints with the structural error are neatly identified.
Where I'm stuck
I want to do a linear regression again on the x's and y's without the data points that should be clipped. In the most ideal case, I'd like to enter the condition (D2:D22<$B$31*$B$32
) directly into the LINEST
formula, so I get the results without using intermediate columns (now in E:G). I'm pretty sure that this can be done using some condition and using array functions, but I can't get it to work.
linest
in F/G you would need to combine the four groups of data in F/G to exclude blanks? This is what you want to do, but without the need for E,F,G - right?=LINEST(N(OFFSET(G2:G22,SMALL(IF(ISNUMBER(G2:G22),ROW(G2:G22)-ROW(G2)),ROW(INDIRECT("1:"&COUNT(G2:G22)))),0,1)),N(OFFSET(F2:F22,SMALL(IF(ISNUMBER(G2:G22),ROW(G2:G22)-ROW(G2)),ROW(INDIRECT("1:"&COUNT(G2:G22)))),0,1)))
- I get around 3#N/A
which we can get withF2=IF($E2,LOOKUP(1,2),A2)
andG2=IF($E2,LOOKUP(1,2),B2)
. This means we can work with #N/A errors, I think that can help us link back to the true/false