Excell

Download the Excel file “Consumer complaints”. It is a big file, be patient. 
Using the data in this file answer the following questions. Please note that you should use the function suggested in the question. Points will be deducted if you do not use the function suggested. 

1. How many complaints are related to Debt Collection? Use the “countif” function and show your answer in an Excel cell

2. Build a new column to generate a number 1 if the “Consumer disputed” data is equal to “Yes”. Use the “if” function. For example, you can say if(A1=”Yes”, 1, ) 

3. Use the “conditional formatting feature” to change the color of the cells that are related to “Incorrect information on your report” in the “Issue” column. Turn those cells green with dark green text.

4. Build a filter in the dataset. Use the filter feature found in the data menu.

5. Concatenate the State and the Zip code in a separate column. Generate a new column with the two values together. Use the function concatenate or concat.

6. Use the function Vlookup to find the “Company response to the customer” for the Complaint IDs given in table 1 in the excel file (Columns U and V). The value should be exact (“False”).

7. BONUS (6 points towards your lab grade).Create a pivot table (Insert menu, pivot chart and table, just do the table and delete the chart) to display the sum of the number of Day open by state and Issue (you can choose the layout and what else to display if anything).