Microsoft Excel: regressieanalyse

Regression analysis is critical in statistics. Consider the data as shown here in the Microsoft Excel worksheet. The scatter plot of variables has a linear trendline and line equation.

To calculate statistics of the straight line that best fits the data, choose the function LINEST.

Select appropriate Y and X variables in the arrays. As a result, two values are returned — the slope and the y-intercept for the straight trendline equation.

Another application of the same function is predicting sales. Based on the following dataset, if one wants to predict sales in the 12th week, the function LINEST can be used in the following way.

The function FORECAST.LINEAR works in the same way. Here, 12, the prediction is the same for the selected arrays and a predetermined X-value.

Similarly, the function TREND returns the dependent parameter values based on the linear trend.

For the same example of weekly sales, select the function TREND, add a new range of X variables — weeks, and the result provides Y variables — sales.