Linear Regression in Excel

Follow us on LinkedIn

Regression analysis consists of a set of statistical methods often used to estimate the relationship between two variables, one independent and the other dependent. It is a useful tool in several cases, especially for modeling and forecasting. Mostly used in statistics, regression analysis can also be beneficial in finance and investing.

When it comes to regression analysis, there are several variations that one can use. Among these, one is the linear regression analysis.

Add your business to our business directory https://harbourfronts.com/directory/ Add your business. Also check out other businesses in the directory

What is Linear Regression?

Linear regression consists of analyzing two different variables to find a single, linear relationship. Usually, investors use it to determine the relationship between price and time. In this case, time is the independent variable, while the price is a dependent variable that fluctuates with time. Through regression analysis, investors can identify the highest and lowest price points, entry price, exit prices, and stop-loss price.

What are some assumptions for Linear Regression?

There are a few assumptions that investors need to make in order to determine the relationship between two variables in linear regressions. Firstly, investors must assume there are two variables, one of which is independent and the other dependent. For the independent variable, investors must consider it as truly independent. Similarly, investors must also assume the data does not consist of any different error variances.

Lastly, investors must also assume a correlation between variables, which means the error terms of each variable must be uncorrelated. In case any of these assumptions are not true, the linear regression analysis will fail.

How to perform linear regression in Excel?

Linear regression is complicated for investors, as it is a statistical tool. Without the proper knowledge, they cannot perform it. However, with the help of Excel, the process becomes much straightforward. The best way to perform linear analysis using Excel is through the use of charts.

For example, an investor wants to perform linear regression on the price of a stock. They have prepared the following information based on historical prices from the stock market.

Year Stock price ($)
2015 89.5
2016 93.6
2017 95.7
2018 96.9
2019 102.7
2020 104.6

 

In this case, the independent variable is the year, and the dependent variable is the stock price. Using a chart, the investor can see the changes in the dependent variable in relation to the independent variable. The best chart for this purpose is a scatter chart.

The data must be selected or highlighted to create a scatter chart. From there, the option to create a scatter chart is available under the ‘Insert’ tab in Excel 2019. It is as below.

It will produce a chart similar to the one below.

The next step is to generate a trendline. The option to create a trendline is online available if the chart is selected. The trendline option becomes available by selecting the chart and going to the tab and clicking on ‘Add chart element’. The easiest way to create a trendline is to use the ‘Linear’ option from the ‘Trendline list’.

However, for Linear Regression, it is critical to click the ‘More Trendline Options’ option, as shown below.

This option will open a new sidebar on the right. By selecting the ‘Linear’ option from the bar, the chart will display a linear line. From there, the options ‘Display Equation on chart’ and ‘Display R-squared value on chart’ will give information related to linear regression, as follows.

It will give information related to linear regression on the chart. The final chart will look similar to the following.

In the above equation, ‘y’ signifies the linear regression of the given data. The ‘R2‘ shows the percentage of variance in the dependent variable that the independent variable explains collectively.

Conclusion

Linear regression is a technique used to determine the relationship between two variables. There are some assumptions that investors need to make to use it. The best way to perform linear regression in Excel is through the use of the charts feature, as explained above.

Further questions

What's your question? Ask it in the discussion forum

Have an answer to the questions below? Post it here or in the forum

LATEST NEWSThe 12 best Spanx Black Friday deals of 2023
The 12 best Spanx Black Friday deals of 2023

Spanx is hosting a Black Friday sale with discounts on their legendary shapewear, as well as deals on our favorite leggings and work pants.

Stay up-to-date with the latest news - click here
LATEST NEWSUkraine's new ATACMS shook Russia's attack helicopter fleet, but experts say Russian Ka-52 'Alligators' are still a threat to the front lines
Ukraine's new ATACMS shook Russia's attack helicopter fleet, but experts say Russian Ka-52 'Alligators' are still a threat to the front lines

The October 17 cluster missile strikes on two Russian airfields destroyed over a dozen helicopters, including several Ka-52s.

Stay up-to-date with the latest news - click here
LATEST NEWSUkrainian forces blew up a dance hall where an actress was performing for Russian troops: reports
Ukrainian forces blew up a dance hall where an actress was performing for Russian troops: reports

The Russian actress was killed in the strike along with 20 Russian soldiers, according to the BBC.

Stay up-to-date with the latest news - click here
LATEST NEWSTarget might limit you to just 10 items at self-checkout registers
Target might limit you to just 10 items at self-checkout registers

Target is experimenting with a 10-item limit at self-checkout kiosks at some stores as the retailer grapples with theft and other forms of product loss.

Stay up-to-date with the latest news - click here
LATEST NEWSAnalysis-Canada's EV charging strategy reaches fork in the road
Analysis-Canada's EV charging strategy reaches fork in the road
Stay up-to-date with the latest news - click here

Leave a Reply