New Perspectives Excel 365 | Module 7: SAM Project B | Module 7: SAM Project B | Module 7 скачать в хорошем качестве

New Perspectives Excel 365 | Module 7: SAM Project B | Module 7: SAM Project B | Module 7

New Perspectives Excel 365 | Module 7: SAM Project B | Module 7: SAM Project B | Module 7 Contact Us: WhatsApp : +92 3209624776 Email : myitlab870@gmail.com WhatsApp Direct Link for Chat https://wa.link/xz3r2a New Perspectives Excel 365 | Module 7: SAM Project B Triangle Home Security SUMMARIZING DATA WITH PIVOTTABLES GETTING STARTED • Save the file NP_EX365_7B_FirstLastName_1.xlsx as NP_EX365_7B_FirstLastName_2.xlsx o Edit the file name by changing “1” to “2”. o If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically. • With the file NP_EX365_7B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet. o If cell B6 does not display your name, delete the file and download a new copy. PROJECT STEPS 1. Ashley Glenn is an account manager for Triangle Home Security in Lansing, Michigan. She has been tracking quarterly security incidents in an Excel workbook and asks for your help in analyzing the data. Go the Incident Report worksheet. This worksheet contains a table named Incidents that lists data about each security incident. Ashley wants you to list the incident type for each reported incident, which corresponds to the data in the Prefix column. Provide the policy types as follows: a. In cell F5, enter a formula using the HLOOKUP function to look up the incident type. Use a structured reference to look up the value in the Prefix column. b. Retrieve the value in row 2 of the Incident_Types named range on the Lookups worksheet. c. Each policy type covers a range of prefix values, so find an approximate match (TRUE). d. Fill the formula into the rest of the Policy Type column, if necessary. 2. Ashley asks you to also list each response type, which corresponds to the code in the Response Code column. Provide the response types as follows: a. In cell I5, enter a formula using the XLOOKUP function to look up the response type. Use a structured reference to look up the value in the Response Code column. b. Use the Codes named range on the Lookups worksheet as the lookup array. Return the corresponding value from the Response_Types named range on the Lookups worksheet. c. Specify an exact match or next larger item (1) as the match mode. d. Fill the formula into the rest of the Response Type column, if necessary. 3. After an incident response, clients provide feedback to Triangle Home Security by rating the company's equipment, value, and service. Ashley entered the ratings in the Incidents table and wants you to calculate the average rating for each incident as follows: a. In cell M5, enter a formula using the AVERAGE function and structured references to average the values in the Equipment to Service columns of the Incidents table. b. Fill the formula into the rest of the Average column, if necessary. 4. The company policy is to contact each client within one day if they experienced an digital incident and within three days if they provide an average rating of 4.0 or less. Include the contact policy information as follows: a. In cell N5, insert a formula using the IFS function. b. Use a structured reference to test whether the Incident Type is "Digital" and display the text "1 day" if it is. c. Use a structured reference to test whether the Average rating is less than or equal to 4 and display the text "3 days" if it is. d. Add a final default argument to test whether any other condition is TRUE and display a blank cell ("") for that condition. e. Fill the formula into the rest of the Contact column, if necessary.