Need help

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 managers notes. In cells J3:J10, use a combination of text functions to return just the managers raise recommendationHigh, 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 todays date in cell F1.

On the Payroll worksheet, enter the following formulas: In cells F6:F13, add a VLOOKUP function that returns the raise recommendationHigh, Standard, or Lowfrom the evaluations named range. In cells G6:G13, add a VLOOKUP function that returns the starting percentage raise based on the raise recommendationHigh, Standard, or Lowin 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 valuenot just formattedrounded 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.01for 1%. Then, change the title to read Final % Increase Comparison Move and resize the chart so the upper left hand corner is in cell F14 and the lower right hand corner is in cell L22. Set the chart title to 11 point font. Set the vertical and horizontal axis labels to 9 point font.

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.

Save the workbook, submit it as directed, and then close Excel.

Addresses Name StreetAddress City State ZipCode RevisedZip Meda Rodate Christy Istas Jason Niese Paul Medina Juanita Gomez Susan Brock Jay Groth Kate Munson 4215 Lupita Rd 17324 Camino Dr 1542E Abeja Ct 1200 Three Trees Rd 5 Park Rd 923 Clark St 622 E. High St 4476 Wilson Blvd Santa Fe Santa Fe Santa Fe Santa Fe Santa Fe Santa Fe Santa Fe Santa Fe NM NM NM NM NM NM NM NM 87505 87501 87509 87508 87500 87501 87511 87513 - 87505 87501 87509 87508 87500 87501 87511 87513 Phone RevisedPhone 5055551812 (505) 555-1812 5055551812 (505) 555-1812 5055551812 (505) 555-1812 5055551812 (505) 555-1812 5055551812 (505) 555-1812 5055551812 (505) 555-1812 5055551812 (505) 555-1812 5055551812 (505) 555-1812 Confidential Internal Evaluations Customer Team Leadership Manager Reliable Innovation Service Collaborator Rank Average Raise Yes 3 4 5 4 4 4 No 3 3 3 4 3.25 No 4 3 4 4 3.75 Yes 4 5 4 4 5 4.4 No 3 3 5 4 3.75 Yes 4 3 3 3 3 3.2 No 4 3 2 2 2.75 No 2 4 4 3 3.25 Name Meda Rodate Christy Istas Jason Niese Paul Medina Juanita Gomez Susan Brock Jay Groth Kate Munson Title Spa Manager Therapist Massage Therapist Coffee Shop Manager Barista Gift Shop Manager Sales Associate Sales Associate Name Meda Rodate Christy Istas Jason Niese Paul Medina Juanita Gomez Susan Brock Jay Groth Kate Munson Manager Raise Recommendations and Notes High Raise. Very high customer ratings on comment cards. Great at appeasing an upset customer. Standard Raise. Standard employee who works with the team well. Standard Raise. Fills in last minute for other employees and has a large satisfied client following. High Raise. Envisioned and lead a redesign of the Terra Cotta Brew that lead to increased sales. Standard Raise. Average employee who has a good rapport with the customers. Low Raise. Average manager who has had some challenging employees. Low Raise. Has received some customer complaints and seems reluctant to get along with other employees. Standard Raise. Good at her job. However, she misses work a lot and never fills in for others who need off. Scale 5 = Far Exceeds Expectations 4 = Exceeds Expectations 3 = Meets Expectations 2 = Below Expectations 1 = Well Below Expectations Today's Date High Bonus Low Deduct Name Meda Rodate Christy Istas Jason Niese Paul Medina Juanita Gomez Susan Brock Jay Groth Kate Munson Years Employed HireDate JobTitle 3/14/2010 Spa Manager 8/12/2009 Therapist 8/12/2009 Massage Therapist 2/1/2011 Coffee Shop Manager 9/30/2007 Barista 5/15/2010 Gift Shop Manager 3/16/2007 Sales Associate 6/4/2005 Sales Associate Standard Raise % 0 2.50% 2 3.00% 5 3.50% 7 4.00% Years Salary Employed $ 61,500 $ 42,100 $ 45,800 $ 35,000 $ 26,900 $ 37,200 $ 28,100 $ 28,700 $ $ Net Payroll Increase Average Raise % 750 500 Raise Recommend Standard Raise % Low Final % New High Bonus Deduction Raise Amt Increase Salary Create Date By Whom Description Employee performance evalution and raise recommendations Workbook Name e02md02_grader_h1_Raises_Your Office.xlsx Mod. Date By Whom Mod. Description Last Version Backup Name Create Date Sheet Name Creator Purpose Addresses Evaluations Payroll List of employee addresses Employee evaluation data Analysis of proposed raises