Answered step by step
Verified Expert Solution
Question
1 Approved Answer
In the Comp Days column, enter a formula with nested IFs to display the number of comp days that an employee will receive based on
In the Comp Days column, enter a formula with nested IFs to display the number of comp days that an employee will receive based on their years of service. The table for Comp Days is on the second worksheet. Remember to use absolute references to the cells in the second worksheet because the formula will be copied to the entire column.
**Please show formula**
Employee ID | Last Name | First Name | Pay Grade | Hire Date | Years of Service | Birth Date | Age | Store | Job Status | Current Salary | Work Anniversary | Eligible for Bonus | Comp Days | Bonus |
1102 | Delosreyes | Lori | B | 7/10/2014 | 3.5 | 4/11/1961 | 55 | Bonham | FT | $ 106,010 | ||||
1106 | Goode | Bari | B | 11/6/2015 | 2.2 | 11/23/1991 | 25 | Graham | FT | $ 42,182 | ||||
1110 | Reams | Linda | B | 12/4/2015 | 2.1 | 10/15/1966 | 50 | Bonham | FT | $ 92,254 | ||||
1114 | Rodriguez | Richard | D | 3/24/2003 | 14.8 | 12/8/1964 | 52 | Graham | FT | $ 69,250 | Yes | No | ||
1118 | Peters | Jessica | C | 5/23/2011 | 6.6 | 2/15/1962 | 54 | Bonham | FT | $ 102,567 | Yes | |||
1122 | Cortez | Nick | D | 8/12/2002 | 15.4 | 10/15/1968 | 48 | Bowie | FT | $ 94,517 | Yes | No | ||
1126 | Millard | Melissa | B | 11/6/2015 | 2.2 | 3/20/1973 | 43 | Garland | FT | $ 51,791 | ||||
1130 | Burns | Brenda | C | 6/10/2010 | 7.6 | 4/20/1966 | 50 | Garland | FT | $ 32,530 | Yes | |||
1134 | Kimball | Susan | C | 1/20/2016 | 1.9 | 3/21/1957 | 59 | Graham | FT | $ 94,502 |
Employee Dates | |
Years of service as of | 12/31/2017 |
Work Anniversary this month | 4 |
Age as of | 1/1/2017 |
Comp Days | |
<=1 Years of Service | 0 |
>1 and <=5 Years of Service | 1 |
>5 and <=10 Years of Service | 2 |
>10 Years of Service | 3 |
Bonus | |
A | 0 |
B | 300 |
C | 600 |
D | 800 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started