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