Texts: Specialized Functions and Data Validation
You want to create a custom lookup function that enables you to locate employee information based on user-defined criteria and employee number. You also want to use conditional math to analyze the salaries of employees without a college degree (No). You will use data validation, a nested INDEX/MATCH function, and the conditional match functions MAXIFS, SUMIF, COUNTIF, and AVERAGEIF.
Create a Data Validation list in cell J7 based on the employee IDs located in the range A6:A25. Add the input message "Select Employee ID" and use the Warning Style Error Alert. Use the Data Validation list in cell J7 to select Employee_ID 90871 and select Last Name in cell K6 to test the function.
Enter a nested INDEX and MATCH function in cell K7 that examines the range B6:H25 and returns the corresponding employee information based on the match values in cell J7 and cell K6.
Enter a conditional math function in cell K14 that calculates the total number of NO employees.
Enter a conditional math function in cell K15 that calculates the total value of NO employee salaries.
Enter a conditional math function in cell K16 that calculates the average value of NO employee salaries.
Enter a conditional math function in cell K17 that calculates the highest NO employee salary.
Apply Currency Number Format to the range K15:K17.
Save the workbook.