Question

What is the formula I need to complete this

Location Job Status Pay Grade Annual Salary Years Service Bonus
Austin CN 2 $54,000 1
Austin CN 1 $66,000 5
New Orleans CN 3 $90,000 6
Nashville CN 3 $55,000 7
Nashville FT 1 $21,840 16
Nashville FT 1 $25,792 4
Austin FT 3 $45,020 26
Nashville FT 1 $23,920 4
Austin FT 1 $32,011 24
Nashville FT 1 $21,840 1
Austin FT 3 $24,752 3
Austin FT 1 $29,120 5
Nashville FT 1 $22,880 7
Nashville FT 1 $22,880 9
Nashville PT 1 $21,299 18
Nashville FT 1 $25,792 16
Nashville PT 1 $21,299 21
Austin FT 1 $39,000 7
Nashville FT 1 $21,840 3
Nashville FT 1 $22,048 1
Nashville FT 1 $26,000 5
Nashville FT 1 $27,560 2
Nashville FT 1 $29,640 4
Austin FT 1 $28,496 6
Home FT 1 $24,752 9
Austin PT 1 $29,016 18
Nashville FT 1 $22,880 23
Home FT 1 $22,880 8
Total

Years of Service Bonus
<3 0
>=3 and <5 $3,000
>=5 and <15 $6,000
>=15 $8,000

Employess also get a bonus based on their years of service.
Put a function in the Bonus column of the table that calculates the bonus each employee should receive based on
their years of service in column E and the criteria stated in I2:I5.
The function must use references to the cells J2:J5 incase the bonus amounts are changes and correctly use absolute references.

Answer 1

To calculate the service bonus, use an IF formula with absolute references to the bonus amounts. Place the formula in the Bonus column for each employee.

Step-by-step explanation:

The formula needed to calculate the bonus for each employee based on years of service and the criteria stated in cells I2:I5 would be:

=IF(E2<3, J2, IF(AND(E2>=3, E2<5), J3, IF(AND(E2>=5, E2<15), J4, IF(E2>=15, J5, 0))))

You need to place this formula in the corresponding Bonus column for each row that represents an employee. This formula uses IF statements to determine the bonus amount based on the conditions given for the years of service. Absolute references to the bonus amount J2:J5 are used so the formula references these cells without changing them when copied to other cells.