please help, its not making sense :(
Assume you have graduated from SHBU and gained employment at the lucrative firm of Funck inc. Your (unreasonable) bost provides you the following data with the directive: I have a meeting in 10 minutes, and I need the following employee list parsed into separate fields (potter shown below) want to see the department name, not just the code (refer to the lookup table) I want the total salary for each department (C49:CS4), and I want a count of the employees who earn less a specified amount initially, set the cut-off salary level at $100,000 (C59), but create your worksheet so that I can dynamically change the salary level in cell C59 and/or the department names in column 8 (849-854) Step 1: Use the pattern shown below to parse the data into separate fields 10 Pattern: Last Name First Name Employee ID1Department code!Salary **You may rearrange the column headings AND/OR ADD new columas if needed Ther are no spaces in the text strings 11 12 Raw Data Parsed Data 13 Last Name First Name Employee ID Department Code Salary-Parsed 14an Becky,8321A21110567 15 Smith Sallie 430P2U194569 16 Sherril, Karen,5587/MA/102309 17 Jesswein Kurt46148922 18 Houston Samuel,7731A111957304 19 Molina Yadier 9221P11254679 20 Stretcher Robert.9231P2095902 21 Howard Jamison,23918829 23 Step 2: Convert the parsed Salary field (from above) into a numeric field using the NUMBERVALUE Function. Place the numeric salary values in the range C26C33. Record Salary Numeric Becky Jillian Sallie Smith Karen Sherril Kurt Jesswein Samuel Houston Theuttite ANARARE 25 27 DO NOT USE THE TEXT TO COLUMNS OPTION TO USE THE TEXT FUNCTIONS DEMONSTRATED IN LECT DO NOT HARD CODE ANY FUNCTION ARGUMENTS 21 Howard Jamison,2391H118829 22 23 Step 2: Convert the parsed Salary field (from above) into a numeric field using the NUMBERVALUE Function. Place the numeric salary values in the range C26:C33, 24 Salary-Numeric Record Becky Jillian Sallie Smith Karen Sherril Kurt Jesswein Samuel Houston Yadier Molina 31 Robert Stretcher Jamison Howard Step 3: Use a lookup function (VLOOKUP, XLOOKUP, or INDEX/MATCH) to display the name of the department in the range C37 C44 (the parsed Department Code above will serve as the lookup value) Department Name Record Department Lookup Table Code Becky Jillian Sallie Smith Karen Sherril MA Name Maintenance Accounting A1 P20 Purchasing Kurt Jesswein PL Payroll Samuel Houston Yadier Molina H Human Resources Robert Stretcher A2 Advertising Jamison Howard 46 Step 4: Use a conditional statement to sum the Salary by departement. The Department Name in column B serves as the criteria so that that I can dynamically change the department names in column 8 and the w 47 48 Total Salary by Department 49 Purchasing 50 Payroll CHAL 14-intanance MA mal NOT somed and Et String 2 + D E CURL MA Sallie Smith Maintenance 39 A1 Karen Sherril Kurt Jesswein Accounting Purchasing P2U 41 Samuel Houston Payroll PL H 42 Yadier Molina Human Resources Robert Stretcher A2 Advertising Jamison Howard 46 Step 4: Use a conditional statement to sum the Salary by departement. The Department Name in column serves as the criteria so that that I can dynamically change the department names in column B and the worksheet will updat 48 Total Salary by Department Purchasing Payroll 51 Maintenance **Ensure you use bose your conditional statement on the numerical NOT porsed salary data 52 Human Resources 33 Advertising Accounting 54 55 56 Step 5: Use a conditional statement to count the number of employees who earn less than $100,000. 57 Initially, set the salary level at $100,000 (C59), but create your worksheet so that I can dynamically change the salary level in cell C59 58 59 Salary Level $100,000 60# Employees salary
