Please help with formulas and functions
On the Payroll worksheet, there is an analysis for raises you need to finish. First, you need to get all the applicable data for making the raise determination on this worksheet. Add the following. In cell F1, enter a formula to always return the current date. In cell range E6:E13, add a DATEDIF function that will calculate the years employed based on today's date in cell F1 and the Hire Date in column B. Remember you are looking for years and not days. In cells F6:F13, you will determine the raise recommendation for each employee based in an Evaluations named range from the Evaluations worksheet. In cell F6, add a VLOOKUP function that returns the raise recommendation of High, Standard, or Low for each employee by exactly matching the Name in column A to the Evaluations named range, column 10. Be sure to use exact match criteria. Copy the formula through F13. Create a named range for the Standard Raise table in cell range A16:19. Assign the name Ralse 6 In colls G6, add a VLOOKUP function that returns the starting raise percentage based on the named range Raise and the number of years employed in column E. Copy the formula through G13. Next, you need to begin determining the employee's final raise. In cell H6, create an IF function that will award anyone with a Raise Recommend of "High" in cell F6 the extra bonus listed in cell F2. All other employees get zero bonus. Copy the formula through cell H13 In cell 16, create an IF function that will give anyone with a Raise Recommend of "Low" in cell F6 a deduction listed in cell F3. All other employees get zero deduction. Copy the formula through cell 113 On the Payroll worksheet, in cell K6, add a calculation that returns the Final % Increase by dividing the Raise Amt by the Salary. Copy the formula through cell K13. In cell L6, add a calculation that returns the New Salary by adding the Salary and Raise Amt. Copy the formula through cell L13. inted Paradise Today's Date High Bonus Low Deduct Net Payroll Increase Average Raise % $ $ 750 500 RESORT & SPA Low Years Employed Raise Recommend Standard Raise % Final % Deduction Raise Amt Increase New Salary High Bonus 5 Name 6 Meda Rodate 7 Christy Istas 8 Jason Niese 9 Paul Medina 10 Juanita Gomez 11 Susan Brock 12 Jay Groth 13 Kate Munson Hire Date JobTitle 3/14/10 Spa Manager 8/12/09 Therapist 8/12/09 Massage Therapist 2/1/11 Coffee Shop Manager 9/30/07 Barista 5/15/10 Gift Shop Manager 3/16/07 Sales Associate 6/4/05 Sales Associate Salary $ 71,500 $ 42,100 $ 45,800 $ 35,000 $ 29,900 $ 37,200 $ 28,100 $ 28,700 $ $ S S . - . - Years Employed Standard Raise % 2.50% 3.00% 3.50% 4.00% 5 Count Raise High Standard Low Total 26 Raise Amount High 28 Standard Low 30 On the Payroll worksheet, there is an analysis for raises you need to finish. First, you need to get all the applicable data for making the raise determination on this worksheet. Add the following. In cell F1, enter a formula to always return the current date. In cell range E6:E13, add a DATEDIF function that will calculate the years employed based on today's date in cell F1 and the Hire Date in column B. Remember you are looking for years and not days. In cells F6:F13, you will determine the raise recommendation for each employee based in an Evaluations named range from the Evaluations worksheet. In cell F6, add a VLOOKUP function that returns the raise recommendation of High, Standard, or Low for each employee by exactly matching the Name in column A to the Evaluations named range, column 10. Be sure to use exact match criteria. Copy the formula through F13. Create a named range for the Standard Raise table in cell range A16:19. Assign the name Ralse 6 In colls G6, add a VLOOKUP function that returns the starting raise percentage based on the named range Raise and the number of years employed in column E. Copy the formula through G13. Next, you need to begin determining the employee's final raise. In cell H6, create an IF function that will award anyone with a Raise Recommend of "High" in cell F6 the extra bonus listed in cell F2. All other employees get zero bonus. Copy the formula through cell H13 In cell 16, create an IF function that will give anyone with a Raise Recommend of "Low" in cell F6 a deduction listed in cell F3. All other employees get zero deduction. Copy the formula through cell 113 On the Payroll worksheet, in cell K6, add a calculation that returns the Final % Increase by dividing the Raise Amt by the Salary. Copy the formula through cell K13. In cell L6, add a calculation that returns the New Salary by adding the Salary and Raise Amt. Copy the formula through cell L13. inted Paradise Today's Date High Bonus Low Deduct Net Payroll Increase Average Raise % $ $ 750 500 RESORT & SPA Low Years Employed Raise Recommend Standard Raise % Final % Deduction Raise Amt Increase New Salary High Bonus 5 Name 6 Meda Rodate 7 Christy Istas 8 Jason Niese 9 Paul Medina 10 Juanita Gomez 11 Susan Brock 12 Jay Groth 13 Kate Munson Hire Date JobTitle 3/14/10 Spa Manager 8/12/09 Therapist 8/12/09 Massage Therapist 2/1/11 Coffee Shop Manager 9/30/07 Barista 5/15/10 Gift Shop Manager 3/16/07 Sales Associate 6/4/05 Sales Associate Salary $ 71,500 $ 42,100 $ 45,800 $ 35,000 $ 29,900 $ 37,200 $ 28,100 $ 28,700 $ $ S S . - . - Years Employed Standard Raise % 2.50% 3.00% 3.50% 4.00% 5 Count Raise High Standard Low Total 26 Raise Amount High 28 Standard Low 30
