Microsoft Office Tutorials and References

In Depth Information

**APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE**

LINEST(known_y’s,known_x’s,const,stats) [Category: Statistical]]

Calculates the statistics for a line by using the "least squares" method to calculate

a straight line that best ﬁ ts your data, and returns an array that describes the

line. Because this function returns an array of values, it must be entered as an

array formula. Guru Tip: See image under FORECAST. For a simple straight-

line regression, you should select a 1 row x 2 column area and type the LINEST

function. Do not press Enter. Instead, press Ctrl+Shift+Enter. The ﬁ rst value is

the slope and the second value is the intercept.

For multiple regression, you might have several columns of causal variables

and one dependent variable. In the image below, daily sales are in column G.

Several drivers of sales are in A:F.

To calculate multiple regression, select a range of blank cells that is ﬁ ve rows

tall and several columns wide. You will need one column for every causal

variable (in this case, six) and one extra column to hold the intercept. Select

7 columns by 5 rows. Type =LINEST(G2:G363,A2:F363,FALSE,TRUE) and

press Ctrl+Shift+Enter. You then need to label the columns and rows. The ﬁ rst

column in the result corresponds to the ﬁ nal causal variable. I always type the

values from F1 to A1 backwards starting in the ﬁ rst column above the LINEST

formula. (Type Cell F1 in J5. Type cell E1 in K5, and so on until you type cell A1