Addresses worksheet, assignment help
Other
I have no clue what I am doing with the two documents below. I need someone who is extremely good with excel. Below is the instructions and I have attached
|
On the Addresses worksheet, in column E, extra spaces and dashes were left in the data by the database in case the zip code had an extension. In cells F7:F14, enter a LEFT function to return the ZipCode without the unnecessary “–“. In column G, the database did not export the formatting for the phone numbers. In cells H7:H14, use Flash Fill to add formatting back to the phone number. To begin, in cell H7, enter (505) 555-1812. |
|
On the Evaluations worksheet, in cells I3:I10, calculate the average numerical ratings for each employee. Only managers get evaluated by the Leadership rank in column H. Column C indicates whether the employee is a manager. Thus, the average calculation should only include the values in column H if the employee is a manager. Even if a rating is given to a non-manager employee in column H, that number should not be included in the average in column I. In cells B13:B20, exporting from the rating system combined the managers raise recommendation of High, Standard, or Low followed by the manager’s notes. In cells J3:J10, use a combination of text functions to return just the manager’s raise recommendation—High, Standard, or Low. The values returned should not have extra spaces before or after the word. |
|
Name the range A3:J10 evaluations. |
|
On the Payroll worksheet, enter the following formulas: In cell F1, enter a formula to always return the current date. In cells E6:E13, enter a DATEDIF function that will calculate the years employed based on today’s date in cell F1. |
|
On the Payroll worksheet, enter the following formulas: In cells F6:F13, add a VLOOKUP function that returns the raise recommendation—High, Standard, or Low—from the evaluations named range. In cells G6:G13, add a VLOOKUP function that returns the starting percentage raise based on the raise recommendation—High, Standard, or Low—in column F from the Standard Raise table in cells A16:B19. |
|
On the Payroll worksheet, perform the following: In cells H6:H13, add a function that will award anyone with a Raise Recommend of High an extra bonus listed in cell F2. All other employees get zero bonus. In cells I6:I13, add a function that will give anyone with a Raise Recommend of Low a deduction listed in cell F3. All other employees get zero deduction. In cells J6:J13, add a calculation that returns the final raise amount with the value—not just formatted—rounded to two decimals. The final raise amount is the salary multiplied by the Standard Raise % and then adding any bonus and subtracting any deduction. |
|
On the Payroll worksheet, perform the following: In cells K6:K13, add a calculation that returns the Final % Increase. The final increase is determined by dividing the Raise Amt by the Salary. In cells L6:L13, add a calculation that returns the New Salary based on the Salary and Raise Amt. |
|
On the Payroll worksheet, In cell J1, add a function that calculates the Net Payroll Increase based on the Raise Amount in cells J6:J13. In cell J2, add a function that calculates the Average Raise % based on the Final % Increase in cells K6:K13. |
|
On the Payroll worksheet, Based on the data in cells A5:A13 and K5:K13, add a 3-D Clustered Column chart. Under Chart Styles, set the chart to Style 3. Change the vertical axis to start at a lower bound of 0.01—for 1%. Then, change the title to read Final % Increase Comparison |
|
On the Documentation worksheet: Insert 12/31/2015 into cells A6, A8, and A20:A22. Type your Your Office into cells B6, B8, and C20:C22. Type Completed the analysis of employee raise recommendations into cell C8. |
