STEM Mentors, a company specializing in education software for High
School STEM teachers. Every July, the company sponsors a conference
to showcase its wares and provide informative speakers and
workshops on technology in science and math education. After the
conference, Robert compiles results from a survey to acts a guide
for the next conference. You’ll help Robert generate a report on
the conference response. In the Survey Results worksheet, the
answers to seven survey questions have been entered in an Excel
table named Survey. The responses for the first four questions are
the letters a through d, which represent responses from “very
satisfied” to “very dissatisfied.” The text of the survey questions
is on the Survey Questions worksheet. Complete the following.
1. In the Survey Results worksheet, in the
Workshops column, display text associated with answers to Q1 by
clicking cell I6 and inserting the VLOOKUP function to do an exact
match lookup with the Q1 field as the lookup value, the
survey_lookup table as the lookup table, the second column of that
table as the return value, and the range_lookup value set to
FALSE.
2.
Repeat Step 1 for the Speakers through Meals field, using
values of the Q2 through Q4 fields. (Hint: You can use AutoFill to
quickly enter the formulas for the Speakers through Meals
fields.)
3.
In the School column, display the type of school of each attendee
(Public, Private, Online, or Tutor) by clicking cell M6 and
inserting the VLOOKUP function to do an exact match lookup of
values in the Q5 field from the school_lookup table, returning
values from the second column of that table, and setting the
range_lookup value to FALSE.
4.
In the Prior Conferences column, indicate the number of conferences
previously attended (0, 1, 2, and 3+) by clicking cell N6 and
inserting an approximate match look up of the values in the Q6
field using the conference_lookup table as the lookup table and
returning the value from the second column of that table.
5.
On the Report worksheet, do the following:
In cell B14, use the COUNTIF function to count the number of
records in the Return field from the Survey table that equal “will
return.”
In cell B15, calculate the difference between cell B11 and
B14.
In the range B18:B21, use the COUNTIF function to count the
number of records of the School field in the Survey table that
equal Public, Private, Online, and Tutor.
In the range B24:B27, use the COUNTIF function to count the
number of records in the Prior Conferences field of the Survey
table that equal 0, 1, 2, and 3+.
In cells C14, C15, C18:C21, and C24:C27, divide the counts you
calculated for each response group by the total number of responses
shown in cell B11 to express the values as percentages.
6.
In the Survey Results worksheet, create a PivotChart, placing it in
cell A4 of the PivotTables worksheet, and then do the following to
analyze what factors might have contributed to a person decided
against returning to next year’s conference:
Name the PivotTable as workshop pivot.
Place the Workshops field in the Columns area, the Return field
in the Rows area, and the ID field in the Values area.
7.
Make the following changes to the PivotChart:
Move the chart to the Report worksheet to cover the range
E7:I17.
Change the chart type to the 100% Stacked Column chart.
Remove the chart legend and field buttons from the chart.
Add the chart title Workshop Satisfaction to the
chart.
Display the table associated with this chart by clicking the
Data Tables check box in the Chart Elements menu. Verify that data
table rows are arranged from top to bottom in the order Very
Satisfied, Satisfied, Dissatisfied, and Very Dissatisfied.
8.
Repeat Steps 6 and 7 to create a 100% Stacked column
chart plotting the Speakers field against the Return field. Place
the PivotTable in cell A10 on the PivotTables worksheet.
Enter speaker pivot as the PivotTable name. Place the
PivotChart in the range K7:O17 on the Report worksheet and
add Speaker Satisfaction as the chart title.
9.
Repeat Steps 6 and 7 to create a 100% Stacked column
chart plotting the Facilities field against the Return field. Place
the PivotTable in cell A16 on the PivotTables worksheet.
Enter facility pivot as the PivotTable name. Place the
PivotChart in the range E19:I29 on the Report worksheet and
add Facility Satisfaction as the chart title.
10.
Repeat Steps 6 and 7 to create a 100% Stacked column
chart plotting the Meal field against the Return field. Place the
PivotTable in cell A22 on the PivotTables worksheet.
Enter meal pivot as the PivotTable name. Place the
PivotChart in the range K19:O29 on the Report worksheet and
add Meal Satisfaction as the chart title.
HELP! I seem to understand step 1 and 2 but I'm stuck on the
rest!! I'll thumbs up !!
- Stem Mentors Robert Harshaw Is An Events Coordinator For Stem Mentors A Company Specializing In Education Software For 1 (43.39 KiB) Viewed 53 times
Lookup Tables a Survey Lookup | 4-Very Satisfied b 3-Satisfied с 2-Dissatisfied d 1-Very Dissatisfied School Lookup a Public b Private с Online d Tutor Conference Lookup 0 0 1 2 1 2 313+ = Documentation Report Survey Results Pivot Tables Lookup Tables Survey
44 X ✓ fx B с D E F G H 1 к L M N STEM Mentors 1 2 PivotTables for PivotCharts 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
SURVEY RESPONSES BROKEN DOWN BY INTENT TO RETURN SUMMARY Responses 257 PLAN TO RETURN will return will not return SCHOOLS Public Private Online Tutor PRIOR CONFERENCES