STEM Mentors Robert Harshaw is an Events Coordinator for 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 act as 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.$

Open the NP$\_$EX$\_7-3.$xlsx workbook located in the Excel$7>$ Case$1$ folder included with your Data Files, and then save the workbook as NP$\_$EX$\_7\_$STEM in the location specified by your instructor.

$2.$

In the Documentation sheet enter your name and the date.

$3.$

In the Survey Results worksheet, in the Workshops column, display text associated with answers to Q$1$ by clicking cell I$6$ and inserting the XLOOKUP function to do an exact match lookup with the Q$1$ field as the lookup value, the survey$\_$lookup range as the lookup array, and the rating$\_$lookup range as the return array.

$4.$

Repeat Step $3$ for the Speakers through Meals field, using values of the Q$2$ through Q$4$ fields. $($Hint: You can use AutoFill to quickly enter the formulas for the Speakers through Meals fields.$)$

$5.$

In the School column, display the type of school of each attendee $($Public$,$ Private, Online, or Tutor$)$ by clicking cell M$6$ and inserting the XLOOKUP function to do an exact match lookup of values in the Q$5$ field from the school$\_$lookup range, and returning values from the type$\_$lookup range.

$6.$

In the Prior Conferences column, indicate the number of conferences previously attended $(0,1,2,$ and $3+)$ by clicking cell N$6$ and inserting the XLOOKUP function to do an approximate match lookup of the values in the Q$6$ field using the conference$\_$lookup range as the lookup array and returning the value from the prior$\_$lookup range. Set the match$\_$mode value to $1.$

$7.$

In the Report worksheet, do the following:

In cell B$14,$ use the COUNTIF function to count the number of records in the Return field from the Survey table that equal $$will return.$$

In cell B$15,$ calculate the difference between cell B$11$ and B$14.$

In the range B$18$:B$21,$ 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 B$24$:B$27,$ 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 C$14,$ C$15,$ C$18$:C$21,$ and C$24$:C$27,$ divide the counts you calculated for each response group by the total number of responses shown in cell B$11$ to express the values as percentages.

$8.$

In the Survey Results worksheet, create a PivotChart, placing it in cell A$4$ of the PivotTables worksheet, and then do the following to analyze what factors might have contributed to a person deciding 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.

$9.$

Make the following changes to the PivotChart:

Move the chart to the Report worksheet to cover the range E$7$:I$17.$

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.

$10.$

Repeat Steps $8$ and $9$ to create a $100\%$ Stacked column chart plotting the Speakers field against the Return field. Place the PivotTable in cell A$10$ on the PivotTables worksheet. Enter speaker pivot as the PivotTable name. Place the PivotChart in the range K$7$:O$17$ on the Report worksheet and add Speaker Satisfaction as the chart title.

$11.$

Repeat Steps $8$ and $9$ to create a $100\%$ Stacked column chart plotting the Facilities field against the Return field. Place the PivotTable in cell A$16$ on the PivotTables worksheet. Enter facility pivot as the PivotTable name. Place the PivotChart in the range E$19$:I$29$ on the Report worksheet and add Facility Satisfaction as the chart title.

$12.$

Repeat Steps $8$ and $9$ to create a $100\%$ Stacked column chart plotting the Meals field