How to Calculate a Simple Linear Regression using Excel

In statistics, simple linear regression analysis consists of only two variables. The two variables are one dependent variable and one independent variable. On this occasion, we will discuss how to calculate a simple linear regression using Excel.

Simple linear regression is important to be used as learning material before moving on to more complex linear regression (read: multiple linear regression). We need to learn the steps of manual calculation of simple linear regression analysis.

It is undeniable that students studying regression analysis need to understand how to do linear regression analysis, starting from the simple ones. This article will give the stages of calculation in simple linear regression analysis.

Before doing calculations manually, it’s good to understand the basic concepts of simple linear regression analysis. In principle, regression analysis is generally chosen to help analyze the impact or influence of one variable on other variables. The influencing variable is called the independent variable. The variable that is affected is called the dependent variable.

Generally, the regression analysis chosen is linear regression analysis. There is a non-linear regression. Please browse the internet or visit the nearest bookstore and look for non-linear regression material. So choosing a simple linear regression analysis is not arbitrary, but there are rules. Some assumptions must be passed. Using the Ordinary Least Square (OLS) method, assumptions in linear regression must be passed. Many call the assumption to test the Gauss Markov assumption.

Assuming the general formula for simple linear regression is well understood, we will obtain coefficient values for both the intercept estimate and the estimation of the variable coefficients from the regression equation. If you remember with econometrics or statistics lessons, this regression estimation coefficient will usually be marked with a hat sign. If you remember this, it means you didn’t sleep when you were in college😊

Well, we’ve been talking ever since! When did you start learning the manual calculation of the regression? Back to the objective of this article 🙂 Okay, to do manual calculations in this simple regression analysis, we can use the usual paper + ballpoint + correction pen and calculator! By the way, do you have a computer? Is there a Microsoft Excel application or something like that? Well, in this digital era, let’s adapt. The calculation steps can be carried out using office applications, such as Excel.

The manual calculation steps for this simple linear regression analysis can calculate easily. I have made the audiovisual, which is divided into three parts; part 1 discusses how to calculate the Coefficient Values of bo, b1, and the Coefficient of Determination (R Square); part 2 discusses how to get the computed F value, and part 3 discusses how to obtain the calculated t value. Each part is discussed in detail and slowly. So don’t be bored if the video is a bit long, but still around 20 minutes per part.

1. How to Calculate bo, b1, and R Square (R2)

An audiovisual of how to calculate the coefficients of bo, b1, and the coefficient of determination (R square) can be seen in the following video (video is made in Indonesian, please use English translation):

2. How to Calculate SSR, SSE, df, MSR, MSE, and F-test value (ANOVA)

Calculate SSR, SSE, df, MSR, MSE, and F-test value (ANOVA) can be seen in the video below (video is made in Indonesian, please use English translation):

3. How to Calculate Variance, Standard Error, and t-Value

An audiovisual of how to calculate variance, standard error, and t-valuecan be seen in the following video (video is made in Indonesian, please use English translation):

If you have watched until the end, the audiovisual will convey the output of a simple linear regression analysis using statistical software. If the result of the manual calculation is the same as the output from the statistical software, it means that the results of the manual analyses that we have done are correct. In the calculation, human error may occur, leading to false.

Well, in the end, if there are still things that need to be discussed together, you can leave a message in the comments column below. Comments under the video are also allowed. See you in the next article! I hope this article will be beneficial for all of us.

Leave a Comment