RATING | |||||||
5 | 4 | 3 | 2 | 1 | |||
Extremely Meaningful | Very Meaningful | Moderately Meaningful | Slightly Meaningful | Not at all meaningful | |||
Extremely Challenging | Very Challenging | Moderately Challenging | Slightly Challenging | Not at all challening | |||
Extemely Often | Very Often | Moderately Often | Slightly Often | Not at all stressed | |||
Extremely Well | Very Well | Moderately Well | Slightly Well | Not at all well | |||
Extremely Realistic | Very Realistic | Moderately Realistic | Slightly Realistic | Not at all realistic | |||
Extremely Satisfied | Very Satisfied | Moderately Satisfied | Slightly Dissatisfied | Not at all satisfied | |||
Extremely Likely | Very Likely | Moderately Likely | Slightly Likely | Not at all likely | |||
Dept ID | Meaningful Work | Challenging Work | Job Stress | Pay | Realistic Expectation | Job Satisfaction | Look for job |
HR | 5 | 5 | 4 | 2 | 4 | 3 | 1 |
OPS | 4 | 2 | 2 | 2 | 3 | 4 | 5 |
FIN | 4 | 5 | 2 | 4 | 3 | 3 | 4 |
PAY | 4 | 3 | 5 | 5 | 3 | 3 | 1 |
OPS | 1 | 3 | 3 | 4 | 4 | 2 | 5 |
OPS | 2 | 5 | 2 | 3 | 1 | 5 | 1 |
MKT | 4 | 5 | 5 | 2 | 2 | 2 | 5 |
OPS | 4 | 2 | 1 | 1 | 5 | 3 | 3 |
OPS | 2 | 2 | 3 | 1 | 4 | 2 | 4 |
OPS | 4 | 5 | 2 | 1 | 1 | 4 | 1 |
OPS | 4 | 5 | 1 | 1 | 1 | 1 | 5 |
HR | 4 | 4 | 3 | 4 | 3 | 3 | 2 |
MKT | 4 | 5 | 3 | 2 | 3 | 2 | 5 |
MKT | 2 | 5 | 5 | 3 | 2 | 3 | 4 |
OPS | 5 | 1 | 3 | 5 | 4 | 1 | 2 |
MKT | 4 | 4 | 5 | 3 | 2 | 3 | 4 |
OPS | 5 | 4 | 1 | 4 | 4 | 4 | 2 |
PAY | 5 | 4 | 4 | 3 | 3 | 2 | 2 |
OPS | 1 | 5 | 3 | 5 | 1 | 1 | 4 |
MKT | 4 | 4 | 5 | 2 | 1 | 3 | 5 |
OPS | 4 | 5 | 3 | 2 | 1 | 3 | 5 |
OPS | 3 | 5 | 1 | 3 | 3 | 4 | 3 |
OPS | 1 | 3 | 2 | 4 | 2 | 2 | 4 |
OPS | 5 | 2 | 2 | 1 | 4 | 3 | 4 |
PROC | 1 | 2 | 2 | 5 | 5 | 2 | 5 |
OPS | 5 | 2 | 2 | 1 | 5 | 4 | 5 |
MKT | 3 | 4 | 4 | 2 | 3 | 3 | 5 |
OPS | 2 | 4 | 2 | 4 | 1 | 1 | 1 |
PAY | 3 | 5 | 5 | 5 | 5 | 4 | 2 |
OPS | 1 | 4 | 3 | 1 | 3 | 4 | 2 |
OPS | 3 | 3 | 4 | 2 | 5 | 2 | 5 |
PROC | 1 | 2 | 2 | 4 | 5 | 2 | 4 |
FIN | 1 | 3 | 4 | 3 | 3 | 1 | 5 |
PROC | 3 | 1 | 2 | 5 | 2 | 4 | 5 |
OPS | 2 | 5 | 3 | 5 | 2 | 5 | 3 |
OPS | 4 | 4 | 4 | 1 | 2 | 1 | 4 |
OPS | 3 | 4 | 1 | 1 | 3 | 1 | 1 |
OPS | 1 | 5 | 3 | 3 | 5 | 5 | 5 |
MKT | 4 | 4 | 5 | 3 | 2 | 3 | 5 |
OPS | 2 | 5 | 4 | 1 | 2 | 2 | 3 |
OPS | 4 | 5 | 5 | 2 | 3 | 1 | 2 |
OPS | 3 | 3 | 4 | 2 | 1 | 2 | 1 |
OPS | 3 | 5 | 1 | 2 | 1 | 5 | 1 |
HR | 4 | 4 | 4 | 4 | 4 | 5 | 1 |
OPS | 1 | 1 | 2 | 3 | 5 | 1 | 1 |
OPS | 2 | 2 | 4 | 2 | 4 | 1 | 3 |
HR | 5 | 5 | 3 | 3 | 3 | 4 | 2 |
HR | 3 | 4 | 3 | 3 | 5 | 4 | 1 |
OPS | 1 | 1 | 4 | 3 | 4 | 4 | 1 |
PAY | 4 | 4 | 4 | 4 | 3 | 3 | 2 |
PAY | 4 | 4 | 5 | 4 | 5 | 2 | 3 |
HR | 5 | 4 | 4 | 4 | 5 | 4 | 1 |
MKT | 3 | 3 | 4 | 3 | 2 | 3 | 4 |
HR | 4 | 4 | 3 | 3 | 4 | 5 | 2 |
OPS | 3 | 1 | 2 | 3 | 1 | 5 | 2 |
MKT | 3 | 5 | 4 | 3 | 3 | 2 | 3 |
PROC | 1 | 3 | 2 | 2 | 4 | 2 | 5 |
PROC | 2 | 2 | 1 | 4 | 4 | 5 | 4 |
OPS | 5 | 5 | 4 | 4 | 2 | 1 | 4 |
OPS | 5 | 1 | 3 | 4 | 4 | 4 | 3 |
OPS | 4 | 1 | 1 | 2 | 5 | 2 | 2 |
OPS | 4 | 5 | 3 | 4 | 1 | 4 | 2 |
OPS | 3 | 2 | 1 | 2 | 2 | 5 | 4 |
HR | 3 | 5 | 3 | 3 | 4 | 4 | 3 |
PROC | 2 | 3 | 1 | 2 | 5 | 4 | 4 |
FIN | 3 | 4 | 5 | 3 | 5 | 2 | 4 |
HR | 5 | 4 | 4 | 3 | 3 | 5 | 2 |
OPS | 5 | 1 | 2 | 1 | 1 | 3 | 3 |
OPS | 1 | 3 | 3 | 2 | 2 | 5 | 1 |
MKT | 2 | 4 | 5 | 3 | 1 | 3 | 5 |
OPS | 3 | 3 | 2 | 2 | 2 | 2 | 1 |
OPS | 2 | 1 | 3 | 4 | 2 | 2 | 1 |
FIN | 3 | 5 | 5 | 3 | 2 | 5 | 5 |
OPS | 5 | 3 | 2 | 5 | 4 | 3 | 5 |
OPS | 3 | 4 | 1 | 5 | 5 | 4 | 1 |
PROC | 2 | 1 | 2 | 4 | 3 | 2 | 5 |
OPS | 4 | 4 | 1 | 4 | 5 | 2 | 2 |
MKT | 3 | 5 | 4 | 4 | 3 | 2 | 5 |
OPS | 2 | 1 | 3 | 5 | 4 | 1 | 3 |
OPS | 3 | 1 | 1 | 2 | 1 | 3 | 4 |
OPS | 3 | 3 | 1 | 5 | 5 | 4 | 4 |
OPS | 3 | 3 | 1 | 3 | 2 | 5 | 5 |
FIN | 2 | 5 | 3 | 2 | 4 | 2 | 5 |
OPS | 1 | 5 | 1 | 1 | 3 | 4 | 5 |
OPS | 3 | 5 | 1 | 5 | 2 | 5 | 5 |
OPS | 1 | 1 | 1 | 2 | 5 | 1 | 5 |
MKT | 3 | 3 | 5 | 3 | 3 | 2 | 5 |
OPS | 5 | 3 | 2 | 3 | 5 | 2 | 2 |
OPS | 4 | 4 | 5 | 2 | 1 | 1 | 4 |
OPS | 4 | 3 | 5 | 1 | 5 | 3 | 4 |
OPS | 4 | 2 | 2 | 5 | 3 | 4 | 4 |
OPS | 5 | 5 | 3 | 3 | 3 | 1 | 4 |
FIN | 3 | 3 | 4 | 3 | 5 | 1 | 1 |
OPS | 2 | 1 | 2 | 5 | 4 | 4 | 2 |
MKT | 3 | 4 | 5 | 4 | 2 | 2 | 4 |
OPS | 4 | 5 | 1 | 3 | 3 | 5 | 2 |
HR | 3 | 5 | 3 | 5 | 5 | 3 | 2 |
FIN | 3 | 2 | 1 | 3 | 1 | 2 | 3 |
PROC | 1 | 3 | 1 | 5 | 4 | 1 | 4 |
OPS | 5 | 3 | 1 | 1 | 5 | 5 | 5 |
Use Excel:
1) Using the data range, insert a pivot table to display the average rating by Dept ID for Q7 of the survey. The average ratings must be converted to 2 decimal places. Make sure to rename the column headings appropriately.
2) Based on this pivot table, insert a Pivot Column Chart. On the chart, locate a column (vertical bar of the column chart), that signifies positive response to this question and change the color of that vertical bar to Green shade. On the chart, locate a column (vertical bar of the column chart), that signifies negative response to this question and change the color of that vertical bar to Red shade. All other vertical bars should be in blue color.
* Please provide steps on how I could complete this in Excel also. Thank You.
Answer:
To insert a pivot table in excel, Click on Insert tab and then Click on PivotTable. Select the location on sheet where you want the pivot table.
1) Put Dept ID in Rows of Pivot Table Builder, "look for job" (Q7) in Values. Change Sum to Average in the Values section of the Pivot Table Builder.
2) Select the pivot table, click on insert and select on Clustered Column chart. You will get the below chart
Now click on the column chart and click on the column "MKT" which has the highest rating. Click again so that only MKT is selected and no other column is selected. Then click on Format tab in the top most ribbon, select shape fill and select Green color. The chart will look like below -
Now click on HR bar with lowest rating and do the same again. Your final chart will look as below -
Get Answers For Free
Most questions answered within 1 hours.