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