Linear regression using Excel

To do advanced statistics in excel you have to make sure that Analysis Toolpak Add-in is installed. Assuming that it is not installed,the following needs to be done;

• Run Microsoft Office Setup to add components to MS Office

• Select Analysis Toolpak under the Excel group of software items.

• Once this is done

• Run Excel

• Select Addins in the tool menu. ( In EXCEL 2007 Add-ins tab is in the excel option dialogue box). In the dialogue box select Analysis Toolpak and then click on the OK button.

Now you have an extra command, Data analysis on the Tools Menu and you can do the statistics.To do advanced statistics in excel you have to make sure that Analysis Toolpak Add-in is installed. Assuming that it is not installed,the following needs to be done;

• Run Microsoft Office Setup to add components to MS Office

• Select Analysis Toolpak under the Excel group of software items.

          Once this is done

• Run Excel

Select Addins in the tool menu. ( In EXCEL 2007 Add-ins tab is in the excel option dialogue box). In the dialogue box select Analysis Toolpak and then click on the OK button.

Now you have an extra command, Data analysis on the Tools Menu and you can do the statistics.

Linear, non-weighted regression

Type your data into blank excel spread sheet.

Now select the data analysis command on the tools menu (Data menu in excel 2007). This will give you dialogue box as shown below, listing all the data analysis facilities that are available. Select Regression and click on the OK button. This will give you a dialogue box full of options.

 Place the cursor in the input range control and then use the mouse to select on the spreadsheet the data you want to analyse. This should enter the appropriate cell reference in the control.

 

Label in first row check box tells the tool to use the labels in the top row of the data to label some of the statistical values. If you haven’t used labels leave this unticked.

• Tick the confidence level check box and enter appropriate values.
• Select the new worksheet ply.
• Select residual plot.
• Click on the OK button.

Excel will do all the calculation and present the results in a new ply.  Lets go through the results step by step:

The slope (m) is the X variable 1 in our case it is 7.05714 x 10^-05 . However if you look along the row you will see 95% confidence limits on m- there is 0.95 probability that m lies between 5.85107E x 10 ^-05 and 8.26322E x 10^-05 . The coefficient c in our equation of straight line is 0.002496 with upper and lower 95% confidence limits of 0.00069 and 0.00429.

Correlation coefficient

R = 0.9856. R represents how closely x variable and y variable vary together .

>> continue reading: Excel can also be used for non- linear regression analysis using the solver add in.

Read about non linear regression analysis.