In: Statistics and Probability
How do I use the data analysis in Excel to figure these questions out?
Suppose a researcher gathered survey data from 19 employees and asked the employees to rate their job satisfaction on a scale from 0 to 100 (with 100 being perfectly satisfied). Suppose the following data represent the results of this survey. Assume that relationship with their supervisor is rated on a scale from 0 to 50 (0 represents a poor relationship and 50 represents an excellent relationship); overall quality of the work environment is rated on a scale from 0 to 100 (0 represents poor work environment and 100 represents an excellent work environment); and opportunities for advancement is rated on a scale from 0 to 100 (0 represents no opportunities and 100 represents excellent opportunities).
Answer the following questions:
Job satisfaction |
Relationship with supervisor |
Opportunities for advancement |
Overall quality of work environment |
Total hours worked per week |
55 |
27 |
42 |
50 |
52 |
20 |
35 |
28 |
60 |
60 |
85 |
40 |
7 |
45 |
42 |
65 |
35 |
48 |
65 |
53 |
45 |
29 |
32 |
40 |
58 |
70 |
42 |
41 |
50 |
48 |
35 |
22 |
18 |
75 |
55 |
60 |
34 |
32 |
40 |
50 |
95 |
40 |
48 |
45 |
40 |
65 |
33 |
11 |
60 |
38 |
85 |
38 |
33 |
55 |
47 |
10 |
5 |
21 |
50 |
62 |
75 |
37 |
42 |
45 |
43 |
80 |
37 |
46 |
40 |
42 |
50 |
31 |
48 |
60 |
46 |
90 |
42 |
30 |
55 |
38 |
75 |
36 |
39 |
70 |
43 |
45 |
20 |
22 |
40 |
42 |
65 |
32 |
12 |
55 |
53 |
Enter the data into Excel.
Now, go to Data > Data Analysis > Regression.
Select the input range and click OK. Check Labels too.
The output is:
y = 98.32909 + 1.323244*x1 + 0.077349*x2 - 0.17004*x3 - 1.52239*x4
R-square = 0.82
Since 82% of the variation in the model is explained, we can say that the estimates are reliable.
Opportunities for advancement is not a good predictor of job satisfaction.(p > .05)
Overall quality of work environment is not a good predictor of job satisfaction.(p > .05)
y = 98.32909 + 1.323244*40 + 0.077349*30 - 0.17004*75 - 1.52239*60 = 49.48292
Please give me a thumbs-up if this helps you out. Thank you!