DOC

# Notes on Using Excel to do simple correlation regression etc

By Heather Cunningham,2014-04-08 20:59
13 views 0
Notes on Using Excel to do simple correlation regression etc

Notes on Using Excel to do simple correlation regression etc.

In excel Tools menu see if you get data analysis as an option, if not, click on Add-ins …

place a check along the box for Analysis Tool pack

Analysis Tool pack VBA

enter data in columns

Type X in row 1,column A and Y in row 1, column B as headings of columns Type numbers 1 2 3 4 6 in first column below the heading X Type numbers 4 11 13 15 40 in second column below Y

click on tools Data Analysis…

find correlation

click on ok

Input Range box type A1:B6 (or wherever your numbers are) Click on Labels in first row. (if you assume the headings are present)

you will get a 2x2 Table in a new worksheet

bottom left box will have the correlation coefficient 0.941457

click on data analysis

click on descriptive stats

click on labels in first row

click on summary stats

click on confidence level for mean

can do covariance etc. like this

if you right click on it a question pops up What is this?

click on the question to get further information.

nd2 set of data:

take numbers for X in the above set and multiply each by 10 you will end up with 10, 20, 30, 40 etc

do not change Y numbers ndnow find covariance and correlation matrices for this 2 dataset

compare the two answers side-by-side with the first dataset.

Variance of x will change

Covariance will change

Correlation will not change

To do Regression of Y on X and Z (Multiple regression)

click on tools Data Analysis…

find regression

click on ok

Input Y Range box A1:B6 (or wherever your numbers are)

Keep your X and Z columns for the data on two rgressors together next to each other and note the starting and ending values of row numbers.

For example if X starts on row 6 and ends on row 36 of column D and

if Z starts on row 6 and ends on row 36 of column E

Input X range (this really is not for x alone but for both X and Z, this is why we need them to be next to each other. In the above example type D6:E36 Note that it is not D36 but it is E36 for the column E)

Click on Labels in first row. (if you assume the headings are present)

DO NOT click on constant is zero (this is for forcing the regression throught the origin) Click on Confidence Level 95%

Click on New Workbook

Residuals

Residual plots

Line Fit plot

Report this document

For any questions or suggestions please email
cust-service@docsford.com