trend/linest regression error

This is a fairly simple section of a spreadsheet I use to find a point on a line defined by two points. I use the =TREND() function for this. I had never noticed this before (maybe because I have been using version 2002 until about a week ago), but on occasion, the TREND() function will return an incorrect result. The behavior I am describing is illustrated in the attached spreadsheet.

Explanation of spreadsheet.

input data for the functions is in columns A through E. In order to understand what the TREND function was doing, I calculated the slope and intercept using both LINEST() and the separate SLOPE and INTERCEPT functions. I observe that LINEST() appears to force slope=0 for some reason. I'm guessing that it is somehow detecting "co-linearity" and consequently declaring the slope 0. I know when I first read that MS had introduced this co-linearity test into the LINEST() function that I was concerned that it would occasionally falsely/erroneously detect co-linearity.

I'm not sure that I really need anyone to propose a solution -- the obvious solution is to use the algebraic function in column Q or use the SLOPE()/INTERCEPT() functions instead of TREND()/LINEST() in this case. What I'm interested in is 1) does this only occur in this one installation of Excel (I will test this tomorrow at work on both 2007 and 2002)? 2) Has anyone else experienced situations where these co-linearity tests have erroneously detected co-linearity and forced needed coefficients to 0?

I have confirmed this behavior in my work installation of 2007. I also confirmed that it does not force slope=0 in 2002, so this works just fine in 2002.

Any insights into this behavior? Is it a mistake to use the TREND() or LINEST() functions to do linear interpolation/extrapolation when you are only using 2 points to define the line?

That's very curious. If you multiply all x & y values by a big-ish constant, the problem goes away.

I had failed to consider the effect the size of the number could have. Even multiplying by 10 so the x values are greater than 1E-4 is enough to get a normal regression. I would have to look through the other spreadsheets using this "subroutine" to see if it always fails when the known_x's are below 1E-4 or if it is still intermittent. Perhaps a few other tests to see if there is a solid threshold value below which it stops working.

I had failed to consider the effect the size of the number could have.

That's certainly no excuse for LINEST

New observation -- the cutoff might be Dx = x2-x1. I added a column to calculate the difference between the known x values and tried multiplying the known x values and newx values by several different multipliers:

1) A multiplier of 1 is obviously the existing scenario.
2) A multiplier of 1.023 "fixes" row 3 Dx(3)=9.5E-7 Dx(4)=4.7E-7 Dx(5)=4.1E-6
3) A multiplier of 2.083 "fixes" row 4 Dx(3)=1.9e-6 Dx(4)=9.5e-7 Dx(5)=8.3e-6
4) A multiplier of 0.239 "breaks" row 5 Dx(3)=2.2e-7 Dx(4)=1.1e-7 Dx(5)=9.5e-7

I'm not certain how the regression algorithm is set up, but this behavior appears to be related to Dx. Perhaps something in the algorithm trying to avoid a div/0 error or perhaps it is assuming that, where x values differ by less than 9.5e-7, the x values are actually equal.

I wonder if this would extend to multipoint regressions where max(x)-min(x)<1e-6?

Additional observations:

I tried 3 data points and 10 data points. While the exact "cutoff" point was not always exactly the same, the cutoff when it would regress correctly was generally when max(x)-min(x) was between 6e-7 and 10e-7, even if the x's are all <100E-7. I guess the take home message is, if one needs to perform regressions using small values for x, it can be important to employ a scaling factor or something so that the x's sent to the regression algorithm are large enough to use.

Hey, Mr. S,

Try this: http://support.microsoft.com/kb/976468

Thank you for that information

I had had that registry mod on my last computer, but got a new laptop this year and had not installed it. Thanks for the prod.