Essentials of Business Analytics 1st Edition by Jeffrey Camm,James Cochran,Michael Fry,Jeffrey Ohlmann ,David Anderson

Exercise 2

Heat maps can be very useful for identifying missing data values in moderate to large data sets. The file Surveyresults contains the responses from a marketing survey: 108 individuals responded to the survey of 10 questions. Respondents provided answers of 1, 2, 3, 4, or 5 to each question, corresponding to the overall satisfaction on 10 different dimensions of quality. However, not all respondents answered every question.
a. To find the missing data values, create a heat map in Excel that shades the empty cells a different color. Use Excel's Conditional Formatting function to create this heat map.
Hint: Click on Conditional Formatting in the Styles group in the HOME tab. Select Highlight Cells Rules and click More Rules …. Then enter Blanks in the Format only cells with: box. Choose a format for these blank cells that will make them obviously stand out.
b. For each question, which respondents did not provide answers Which question has the highest nonresponse rate