How TREND interacts with LINEST in Excel?

[Vandam]

Hello everyone,
I am just going through the topic of trend which is the statistical function used in an Excel. I also know the basic things about the Linest in Excel. The only thing I am not able to understand is the interaction of the trend with the Linest in Excel. So need your help in this topic. Please tell me how TREND interacts with LINEST in Excel? Hope that someone would provide the needful information soon.!!

[Glenny]

To illustrate the trend collinearity, you will have to create an Excel spreadsheet empty with the appropriate coding. Otherwise, you can copy the table from the Microsoft site, and then paste it to your table. After pasting the table into your new Excel worksheet, click Paste Options, then click Match formatting destination. In the pasted range still selected, use one of the following procedures, depending on the version of Excel you are running :

• In Microsoft Excel 2007, click the Home tab, click Format in the Cells group, and then adjust the width of the column.
• In Excel 2003, point to Column on the Format menu, then click AutoFit.

[Eric B]

Trend data are in cells A1: C8. (Entries in cells D2: D6 are not part of data but are used for illustration later in this article.) Trend results for two different models for earlier versions of Excel and later versions of Excel are presented in cells E10: E16 and I10: 116 cells, respectively. Results in cells A10: A16 is the version of Excel you use. If trend is to return appropriate results, then LINEST had better generate appropriate results in step 3. Here the problems come from collinear predictor columns.

[Macarenas]

TREND and LINEST Interaction can be viewed as follows :

1. You call trend (known_y, known_x, new_x 's constant).
2. TREND calls LINEST (known_y's, known_x's, constant, TRUE).
3. Regression coefficients by this call to LINEST are obtained, these coefficients appear in the first line of output table LINEST.
4. For each line new_x, the expected value y is calculated according to these factors and LINEST new_x values in this line.
5. The value calculated in Step 4 is returned in the appropriate cell for output pattern corresponding to this line new_x.

[kattman]

Predictor columns are (argument known_x) collinear if at least one column, c, can be expressed as a sum of multiples of others, c1, this and other columns. Column C is often called redundant because it contains information that can be constructed from the columns c1, this and other columns. The fundamental principle in the presence of collinearity is that results should be unaffected by including or removing a redundant column of original data. For LINEST in Microsoft Excel 2002 and in earlier versions of Excel did not look for collinearity, this principle was easily violated. Predictor columns are almost collinear if at least one column, c, can be expressed as almost equal to a sum of multiples of others, c1, this and other columns.

[chroma]

In Excel 2003 and later versions of Excel, such a message is not sent in an alert or a text string, but in the output table LINEST. TREND has no mechanism for distributing such a message for you. In the LINEST output table, a regression coefficient is zero and whose standard error is zero corresponds to a coefficient of a column that was removed from the model. LINEST output tables contained in rows 23 to 35 corresponding to the trend in output lines 10 to 16. The entries in cells I24: I25 show a column removed redundant predictor. In this case, LINEST chose to remove the column C (coefficients in cells K24 I24, J24, correspond to columns C, B and Excel is constant column, respectively). When collinearity is present, any one of the columns involved can be removed and the choice is arbitrary.