Excel Case #3

Excel Skills: AVERAGE Function, Cell Formatting, COUNTIF Function, MODE Function, MEDIAN Function, PivotChart, PivotTable, SUM Function, Worksheet Formatting

After retiring a few years ago, Dr. Earl Klein founded Klein Technology Seminars (KTS).  KTS offers information technology seminars to local corporations and

continuously strives to provide its corporate clients with quality, timely instruction.  As part of its quality-first strategy, seminar students are asked to complete

customer satisfaction surveys.  After reviewing the surveys, Dr. Klein makes adjustments to the company’s resources.  To facilitate his analysis of the customer

satisfaction surveys, Dr. Klein asks you to develop a Survey Results Workbook.

Dr. Klein hands you a copy of an incomplete workbook called Klein Survey (KleinSurvey.xlsx).  The Response worksheet contains raw survey responses for a two-week

period.  For each question on the survey, the responses are given a unique number.  For example, if a respondent answers “no” for Question 1, then Dr. Klein records

“1” in the worksheet cell.  The Coding Sheet contains the code values for the questions on the customer satisfaction surveys.  First, Dr. Klein wants you to format the

Response worksheet to make it look like Figure 1.  You will need to add the column “Survey Respondent” and insert a title.

Figure 1:  Response Worksheet

Figure 2:  Summary Worksheet
Next, Dr. Klein wants you to add a 3rd worksheet to the workbook and name it “Summary,” as shown in Figure 2.  The purpose of this worksheet it to summarize the survey

response data contained in the Response worksheet.  For each question, the Summary worksheet should provide a count for each possible response.  The count should

reflect the number of times a particular response for the question was given.  First, for the Class Size, use the AVERAGE, MODE, and MEDIAN Functions to summarize the

class size data.  Next, use the COUNTIF Function to summarize the data for location, questions, and the count for each of the course numbers.  Use the SUM Function for

the item “Attended a Seminar”, and use the COUNTIF Function to find those that never attended a seminar.
Create a PivotChart to summarize the response data by course number and place the chart on a new worksheet titled “SatisByCourse.”  See Figure 3. (Experiment with

titles and legends; you do not have to make yours look exactly like Figure 3.)

Figure 3: PivotChart
On a new worksheet titled “SatByInstr,” create a PivotTable to summarize the response data for question 3 by instructor.  See Figure 4.

Figure 4: PivotTable

PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT