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. 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 guessing that it is somehow detecting 'co-linearity' and consequently declaring the slope 0. The LINEST function calculates the statistics for a line by using the least squares method to calculate a straight line that best fits your data, and then. I observe that LINEST() appears to force slope=0 for some reason. 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. Input data for the functions is in columns A through E. second TRUE specifies that you wish the error estimates to be listed. The number of rows in the input range is less than the number of columns in the total range (x-value plus y-value). The Excel spreadsheet function linest is a complete linear least squares curve. The syntax for the LINEST function in Excel is as follows: LINEST(knowny’s, knownx’s, const, stats) knowny’s: The dependent values of the line equation (y values) Please check input ranges again LINEST returns the F statistic, whereas FTEST returns the probability Linest Function. The behavior I am describing is illustrated in the attached spreadsheet. The output returned from LINEST may be incorrect if one or more of the following conditions are true: The range of x-values overlaps the range of y-values. evaluated using Excel and the LINEST function.

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.

This is a fairly simple section of a spreadsheet I use to find a point on a line defined by two points.

Excel linest function returns error