In this example, we are going to calculate the slope, the intercept, and the statistical values for a dataset, including x and y values. Now let’s see how the LINEST function works for simple and linear regression: Simple Linear Regression
Since the LINEST function returns an array of outputs, it must be entered as an array formula using Ctrl+Shift+Enter shortcut. (It is also called the uncertainty of intercept)Note: If the variable is zero, returns #N/A error The standard error values for slope coefficients (m i) of independent variables (x i) ( It is also called the uncertainty of slope) The intercept of the line (the constant value of y when x=0) The LINEST function returns an array of the statistical parameters which, includes: Note: For the logical arguments, you can type TRUE or FALSE, too. 0 is FALSE and means that Excel will not calculate the statistical parameters. 1 is TRUE and means that Excel will return the statistical parameters.
If omitted, Excel will consider it as zero. 0 is FALSE and means that b should be considered TRUE. 1 is TRUE and means that b should be calculated normally. : This is the function’s logical argument, which says how the intercept should be treated (optional). If omitted, Excel assumes the dataset is with the same size as y values. : The independent values of the line equation (x values). Known_y’s: The dependent values of the line equation (y values). The syntax for the LINEST function in Excel is as follows: The LINEST function uses the least square method to do linear regression calculations. In Excel, the LINEST function is used to do linear regression. Y=m 1x 1+ m 2x 2+…+m nx n+b LINEST Function The equation for multiple linear regression is: In this equation, m is called “slope” and b is called “intercept.” The equation for the simple linear equation is: If we have one independent variable, the regression is called simple linear regression, but when we have more than one independent variable, it is called multiple linear regression. The use of linear regression is to estimate the value of the dependent variable using the independent variable and the regression equation. Introduction to Linear RegressionĪs mentioned above, in linear regression, we have an independent variable and a dependent variable, so we seek to examine the relationship between the dependent and independent variables. In this blog, we are going to introduce the LINEST function and discuss how it works. Among Excel statistical functions, the LINEST function is used for linear regression calculations. In statistical modeling, regression means studying the relationship between a variable (dependent variable) and one or more variables (independent variable).