GROWTH - The predicted exponential growth using existing values. LOGEST - The array of values for an exponential curve that best fits the data. This would be a better representation than using a linear regression.Įxcel includes several array functions for performing exponential regressions: STEYX - The standard error of the predicted y-values for each x regression.Īn exponential regression produces an exponential curve that best fits a single set of data points.įor example an exponential regression could be used to represent the growth of a population. SLOPE - The slope of a linear regression line through the given data points. TREND - The y-values along a linear trend given a set of x-values.įORECAST.LINEAR - The future value along a linear trend by using existing values. Provides the standard errors of estimates and a ridimentary analysis of variance ? LINEST - The array of values for a straight line that best fits the data. INTERCEPT - The intersection with the y-axis using a linear regression plotted through known values. This assumes that growth will remain linear for the next year.Įxcel includes several array functions for performing linear regressions: Observations - The number of data points.Ī linear regression produces the slope of a line that best fits a single set of data points.įor example a linear regression could be used to help project the sales for next year based on the sales from this year. Standard Error - This should really be labelled as 'estimated standard deviation of errors' R Square - The square of the correlation coefficient. Multiple R - This is the correlation coefficient and is the square root of 'R Square'. Normal Probability Plots - Select to generate a chart that plots normal probability. Line Fit Plots - Select to generate a chart for predicted values versus the observed values.

Residual Plots - Select to generate a chart for each independent variable versus the residual. Standardized Residuals - Select to include standardized residuals in the residuals output table. Residuals - Select to include residuals in the residuals output table. New Workbook - Click to create a new workbook and paste the results on a new worksheet in the new workbook. To name the new worksheet, type a name in the box. New Worksheet Ply - Click to insert a new worksheet in the current workbook and paste the results starting at cell A1 of the new worksheet. Allow at least seven columns for the summary output table, which includes an anova table, coefficients, standard error of y estimate, r2 values, number of observations, and standard error of coefficients. Output Range - Enter the reference for the upper-left cell of the output table. In the box, enter the confidence level you want applied in addition to the default 95 percent level.Ĭonstant is Zero - Select to force the regression line to pass through the origin. Clear if your input has no labels Excel generates appropriate data labels for the output table.Ĭonfidence Level - Select to include an additional level in the summary output table. Labels - Select if the first row or column of your input range or ranges contains labels. The maximum number of independent variables is 16. Microsoft Excel orders independent variables from this range in ascending order from left to right. Input X Range - Enter the reference for the range of independent data. Specify the output to be pasted in cell 'B11' below this table. Specify the input Y range as C2:C8 and the input X range as D2:D8. Press OK to display the Regression dialog box. Select (Data > Data Analysis) and choose Regression in the Data Analysis dialog box. Lets assume we have the following table of data. The line is called the 'least square fit' and the process of finding this line is called 'least square fit regression' 'the line where the sum of squares of the differences to all data points has the smallest possible value' The best fit line can be defined mathematically as: This is known as the method of least squares.Īll linear regressions take the equation y = mx + b It can be shown mathematically that the best line is one that minimises the total of the squared deviations. You can analyze how a single dependent variable is affected by the values of one or more independent variables. Regression attempts to show the relationship between two variables by providing a mean line which best indicates the trend of the co-ordinates. The Regression analysis tool performs linear regression analysis by using the 'least squares' method to fit a line through a set of observations.

Regression data analysis tool in excel