You can perform regression analysis in two ways:
- Using Data analysis tool
- Using Scatter Plot
Let’s look at these two methods in detail.
Data Analysis Tool: First check data analysis tool pack is installed or not. You can check it by activating data ribbon and at right hand side Data Analysis tool is available or not. If it is not then follow the below process:
Go to Excel Options and Click on Add-Ins on the left side of the page.
Find Analysis tool pack. If it’s on your list of active add-ins, you’re set. If it’s on your list of inactive add-ins, look at the bottom of the window for the drop-down list next to Manage, make sure Add-Ins is selected, and hit Go.
In the next window that pops up,make sure Analysis tool pack is checked and hit OK to activate. Allow it to install if necessary.
Now to perform linear regression, go to the Tools Menu and select Data Analysis. From the Data Analysis window select Regression. That will open a wizard that will look like the picture below:
Now in this window, we can provide Input Y and X Range box and provide ranges of numbers, you want to analyze. After this you can select residuals, output options as you required.
In output sheet, you can see statistical measures to analyse the model fit.
- Plot Scatter graph for both variables and enable trend line.
- Enable equation and R-square for trend line.
Hope this helps!