Years of Service for Benefit Calculation

**monid78** · 02-06-2013, 11:03 AM

I need to adjust the years of service for my benefit calculation. For employees that worked under less than 5 years, the benefit should be 5, for employees that worked over 30 years, the benefit should be 30, the years of service doesn’t need to be adjusted for anybody else. Please see the attached example.
Thank you.
Benefit Service.xlsx

**Pete_UK** · 02-06-2013, 11:06 AM

Put this in C2:

=MIN(MAX(B2,5),30)

then copy down.

Hope this helps.

Pete

**benishiryo** · 02-06-2013, 11:12 AM

hi monid78. so just 2 of these conditions or like what you put in the file? for just 2 conditions, then:
=IF(B2<=5,5,IF(B2>=30,30,""))

if not, then:
=LOOKUP(B2,{1;12;17;24;30},{5;12;17;24;30})