Correlation and Regression in Excel: I was given two long lists of variables and
ID: 3363614 • Letter: C
Question
Correlation and Regression in Excel: I was given two long lists of variables and have done the first section, Correlation (the results of this are pasted beneath the instructions). Can you explain to me what I am being asked to do in Regression question 3. I don't understand what values I'm meant to be inputting into: a. the Y input range, and b. the X input range.
Instructions:
Purpose: The overall goal of the learning labs is for you to learn the basic techniques involved in working with data sets. These techniques include basic summation principles, graphing data, and analyzing data with statistical techniques you will learn in this course.
The goal for this learning lab is to teach you how to calculate a correlation between two variables using Excel.
Introduction: In this learning lab, we will be looking at a hypothetical set of data on the relationship between intrinsic motivation and job performance. One could imagine that intrinsic motivation was measured through the use of a questionnaire and job performance was rated by a supervisor. Let’s imagine that the ratings for intrinsic motivation involved a 7-point Likert scale, with higher scores corresponding to higher intrinsic motivation. Job performance was rated on a 10 point scale, with higher ratings corresponding to better performance. In addition, let’s assume that the researchers hypothesize that there is a positive relationship between these factors, such that as intrinsic motivation increases job performance increases as well.
Correlation
1. Open StatPlus
2. Click “Spreadsheet” at the top of the screen and choose Excel.
3. Open the file PSYCH200_Lesson_13_Learning_Lab.xls
4. Click the StatPlus icon to display the StatPlus tool bar
5. Click on “Statistics” and choose “Basic Statistics and Tables” and “Linear Correlation (Pearson)…”
6. You will be asked to choose the variables – do this by clicking the icon to the right of the Variables window and highlighting cells A1 to B201 (A1 to A201 and B1 to B201).
7. Click again on the StatPlus icon, and you will see the ANOVA window. The Variables window should display “[PSYCH200_Lesson_13_Learning_Lab.xls]Sheet1!$A1:$B201”
8. Click OK
9. A new window will appear with the results of your analysis. Save this file as an Excel file with a name that has the following structure: PSYCH200_LL13_your last name.xls
10. In this table, cell C10 indicates the correlation between intrinsic motivation and job performance.
Regression
1. Click the StatPlus icon to display the StatPlus tool bar
2. Click on “Statistics” and choose “Regression” and “Linear Regression”
3. Based on the hypothesis above input the values being predicted into the Y input range and the values being used to make the prediction in the X input range
4. Hit OK.
5. A new window should now open that shows the results of the Regression analysis.
6. To save your work, copy cells A1:H27 and paste them into the Excel file you saved in Step 9 above, beginning in cell A21.
Making inferences
In the results of your regression analysis, you are given the values for r (Multiple R) and r2 (R Square). Notice that the value for Multiple R matches the value for the correlation we found earlier.
Below the regression statistics, you find an ANOVA table. In this table, the p value for significance is listed in to the right of the F value, in the row labeled “Regression” on the left. In a cell to the right of the ANOVA table, indicate whether the results suggest that there is or isn’t a relationship between intrinsic motivation and job performance.
Below the ANOVA table, you can find the coefficient for the intercept and for X Variable 1. The coefficient for the intercept corresponds to a in the regression equation, and the coefficient for X Variable 1 corresponds to b (the slope) in the regression equation. Write the regression equation (y = bx + a) for these data in a cell to the right of the row listing the interecept, rounding to two decimal points.
Here are my results from the Correlation section:
1 Correlation Coefficients Matrix values removal 2 Mis Pairwise deletion Intrinsic Motivation Job Performance 5 Intrinsic Motivation R Standard Error HO (5 0.16169 0.00492 2.30551 0.02217 10 Job Performance R Standard Error 12 13 HO (5 15 16 R 17 18 Job Performance vs. Intrinsic Motivation 19 20 21 Variabve vs. Variabve No# of valid cases 200Explanation / Answer
Here correlation coefficient you got is
r=0.16169
means
very weak relationship exists between intrinsic motivation and job performance
Here 200 observations used.
Now coming to your doubt
We take Job performnace on Y axis
and Intrinsic motivation on X axis
We are predicting Job performance based on Intrinsic motivation.
values being predicted into the Y input range is
B1 to B201 which are jopb performance values.
and
the X input range. is A1 to A201 which are intrinsic motivation values.