## Formula to Calculate Regression

Regression formula is used to assess the relationship between dependent and independent variable and find out how it affects the dependent variable on the change of independent variable and represented by equation Y is equal to aX plus b where Y is the dependent variable, a is the slope of regression equation, x is the independent variable and b is constant.

Regression analysis widely used statistical methods to estimate the relationships between one or more independent variables and dependent variables. Regression is a powerful tool as it is used to assess the strength of the relationship between two or more variables and then it would be used for modeling the relationship between those variables in the future.

**Y=a + bX + ∈**

Where:

- Y – is the dependent variable
- X – is the independent (explanatory) variable
- a – is the intercept
- b – is the slope
- ∈ – and is the residual (error)

The formula for intercept “a” and the slope “b” can be calculated per below.

a= (Σy)(Σx^{2}) - (Σx)(Σxy)/ n(Σx^{2}) - (Σx)^{2}b= n(Σxy) - (Σx)(Σy)/n(Σx^{2}) - (Σx)^{2}

### Explanation

Regression analysis as mentioned earlier is majorly used to find equations that will fit the data. Linear analysis is one type of regression analysis. The equation for a line is y = a + bX. Y is the dependent variable in the formula which one is trying to predict what will be the future value if X an independent variable change by certain value. “a” in the formula is the intercept which is that value which will remain fixed irrespective of changes in the independent variable and the term ‘b’ in the formula is the slope which signifies how much variable is the dependent variable upon independent variable.

**Examples**

#### Example #1

Consider the following two variables x and y, you are required to do the calculation of the regression.

Solution:

Using the above formula, we can do the calculation of linear regression in excel as follows.

We have all the values in the above table with n = 5.

Now, first, calculate the intercept and slope for the regression.

Calculation of Intercept is as follows,

a = ( 628.33 * 88,017.46 ) – ( 519.89 * 106,206.14 ) / 5* 88,017.46 – (519.89)^{2}

a = 0.52

Calculation of Slope is as follows,

b = (5 * 106,206.14) – (519.89 * 628.33) / (5 * 88,017.46) – (519,89)^{2}

b = 1.20

Let’s now input the values in the regression formula to get regression.

Hence the regression line** Y = 0.52 + 1.20 * X **

#### Example #2

State bank of India recently established a new policy of linking savings account interest rate to Repo rate and the auditor of the state bank of India wants to conduct an independent analysis on the decisions taken by the bank regarding interest rate changes whether those are been changes whenever there have been changes in the Repo rate. Following is the summary of Repo rate and Bank’s savings account interest rate that prevailed in those months are given below.

The auditor of state bank has approached you to conduct analysis and provide a presentation on the same in the next meeting. Use regression formula and determine whether Bank’s rate changed as and when Repo rate was changed?

Solution:

Using the formula discussed above, we can do the calculation of linear regression in excel. Treating Repo rate as an independent variable i.e. X and treating Bank’s rate as the dependent variable as Y.

We have all the values in the above table with n = 6.

Now, first, calculate the intercept and slope for the regression.

Calculation of Intercept is as follows,

a = ( 24.17 * 237.69 ) – ( 37.75 * 152.06 ) / 6 * 237.69 – (37.75)^{2}

a = 4.28

Calculation of Slope is as follows,

b = (6 * 152.06) – (37.75 *24.17) / 6 * 237.69 – (37.75)^{2}

b= -0.04

Let’s now input the values in the formula to arrive at the figure.

Hence the regression line **Y = 4.28 – 0.04 * X**

**Analysis:** It appears State bank of India is indeed following the rule of linking its saving rate to repo rate as there is some slope value that signals a relationship between repo rate and bank’s saving account rate.

#### Example #3

ABC laboratory is conducting research on height and weight and wanted to know if there is any relationship like as the height increases weight will also increase. They have gathered a sample of 1000 people for each of the categories and came up with an average height in that group.

Below are the details that they have gathered.

You are required to do the calculation of regression and come up with the conclusion that any such relationship exists.

Solution:

Using the formula discussed above, we can do the calculation of linear regression in excel. Treating Height as independent variable i.e. X and treating Weight as the dependent variable as Y.

We have all the values in the above table with n = 6

Now, first, calculate the intercept and slope for the regression.

Calculation of Intercept is as follows,

a = ( 350 * 120,834 ) – ( 850 * 49,553 ) / 6 * 120,834 – (850)^{2}

a = 68.63

Calculation of Slope is as follows,

b = (6 * 49,553) – (850 *350) / 6 * 120,834 – (850)^{2}

b = -0.07

Let’s now input the values in the formula to arrive at the figure.

Hence the regression line** Y = 68.63 – 0.07 * X**

**Analysis:** It appears that there is a significant very less relationship between height and weight as the slope is very low.

### Relevance and Uses of Regression Formula

When a correlation coefficient depicts that data can predict the future outcomes and along with that a scatter plot of the same dataset appears to form a linear or a straight line, then one can use the simple linear regression by using the best fit to find a predictive value or predictive function. Regression analysis has many applications in the field of finance like its used in CAPM that is the capital asset pricing model a method in finance. It can be used to forecast revenue and expenses of the firm.

### Recommended Articles

This has been a guide to Regression Formula. Here we learn how to calculate regression using its formula along with practical examples and downloadable excel template. You can learn more about excel modeling from the following articles –

- Examples of Gini Coefficient Formula
- Correlation Formula | Examples
- Excel Forecast Formula
- What is the Coefficient of Variation Formula?
- Regression vs ANOVA – Compare
- FORECAST Function in Excel
- Correlation Excel Matrix
- LINEST Function in Excel

- 250+ Courses
- 40+ Projects
- 1000+ Hours
- Full Lifetime Access
- Certificate of Completion