Doing Statistical Analysis with MS Excel - How to make a correlation plot and regression analysis
(Page 4 of 4 )
A correlation plot/regression analysis is another important data analysis tool that can determine the degree of relationship between two variables. For example, say you have the following data:
We would like analyze the relationship between Data1 and Data2.
Below are the steps you need to take to make the correlation plot:
Step 1: Paste/Construct the data table above, starting in cell A1 to cell C11.
Step 2: Click "Insert" -> "Chart" -> "XY Scatter," and then click Next.
Step 3: In the Data range, select cells B2 to C11, and then click "Finish."
The scatter plot will now be generated. To find out the model of the regression analysis plot, click on any dot in the scatter plot, right click on it, and then click "Add Trend line."
Step 4: Finally, select any regression type of your choice. For example, we will select the "linear" type regression. To display the model/equation in the graph, click the "Options" tab, and then click "Display Equation on Chart" and also "Display R-squared value on chart."
To interpret the analysis plot, R squared (in statistics this is called coefficient of determination) of 0.989 means that Data 1 and Data 2 are 98.9% related (this percentage indicates how well they fit each other).
Conclusions
MS Excel is a highly powerful spreadsheet that offers statistical analysis tools to make histograms and perform correlation/regression analysis. The above tutorial shows that it can be done without installing the Data Analysis tool pack.
The data used in this article was purely for example purposes; you can have the data on your own and make your own statistical analysis. In upcoming articles I will discuss another set of statistical analysis such as comparing the means of two data sets as well the variation.
If you are in the engineering/manufacturing sector, or a beginner in the field of statistics, you will find this tutorial very helpful.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
