Chapter 3, Problem 19P

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.

1. 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.

2. b. For each question, which respondents did not provide answers? Which question has the highest nonresponse rate?