19. In cell O2, enter a formula using nested IF functions to calculate the maxim

Question

19. In cell O2, enter a formula using nested IF functions to calculate the maximum amount of 401(k) contributions that the company will match for an employee. The company’s 401(k) matching plan is based on an employee’s job status (shown in column G) and years of service (shown in column M). Use the following parameters and noted reference types when creating this formula: a. If an employee’s Job Status field value is equal to FT, the employee is eligible for the 401(k) matching program. Use a structured reference to the Job Status field in the formula. b. If the FT employee also has a Years Service field value greater than or equal to 15, the Max 401(k) Company Match amount is calculated by multiplying the employee’s Current Salary by the value in cell T3. Use structured references to the Years Service and Current Salary fields and an absolute reference to cell T3 in the formula. c. If the FT employee does not have a Years Service value greater than or equal to 15, the Max 401(k) Company Match amount is calculated by multiplying the employee’s Current Salary by the value in cell T4. Use structured references to the Years Service and Current Salary fields and an absolute reference to cell T4 in the formula. d. If the employee’s Job Status field value is not equal to FT, the formula should return a value of 0. The formula should automatically fill into the range O2:O101. (Hint: If the formula does not fill into that range, copy the formula from cell O2 to the range O3:O101.)

Explanation / Answer

what have you tried in cell o2 so far? go ahead and post the formula you have been struggling with, and then it might just be a minor tweak to get the formula working.
struggling is how you learn.

-edit

ok so theres a few problems here
1. you are using a named range [job status] and [years of service].....you can start by keeping it simple and use 2 cells that are easy to test
so instead of [job status] use G2
and instead of [years of servie] use M2

2. the "current salary" and you need it, so i will randomly guess salary in J2 (thats probably the reason no one wanted to Answer this Question)

normally you would use an IF() with an AND(), but this requires a stupid Nested IF()
so your final Nested IF() will look something like this

=IF(G2="FT", IF(M2>=15,J2*$T$3, J2*$T$4),0)

then change stuff in the cells to test it out

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.