A blog about free research software, alternative to commercial paid software.

One way to help people is to share your knowledge with those who could benefit from it. As a researcher , constant effort to solve scientific problems have exposed me to various scientific resources that can be accessed free of charge. Especially these knowledge is useful to labs and institution unable to access expensive software or novice users due to lack of expertise. In this blog, I have provided a comprehensive list of freely available softwares, alternative to commercial software. I shall share with you all that I have learned, as I make the changes or learn something new, I shall document them. Hope this blog will help those finding the way………….

Research software and books

Dec 25, 2010

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.



0 comments: