if statements

[Guest]

I am trying to find a formula/function that will calculate redundancy pay
using the following criteria

Up to 10 years service, 1 month pay per years service.

After 10 years, 1.25 months pay per years service.

After 15 years, 1.5 months pay per years service

Therefore if an individual has 20 years service the firstt 9.99 years would
receive 1 months pay per years service years 10 to 15 would receive 1.25
months pay per years service and 15+ would receive 1.5 months pay per years
service

I hope somenone can help

 

[Bob Phillips]

=MIN(service,10)*salary/12+MIN(MAX(service-10,0),5)*salary*1.25/12+MAX(servi
ce-15,0)*salary*1.5/12

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

=(MIN(A1,10)+MEDIAN(0,5,A1-10)*1.25+MEDIAN(0,A1-15,99)*1.5)*A2/12

A1=Years service
A2=Annual salary

 

[Guest]

Try

=A2/12*(A1+(MAX(0,A1-10)+MAX(0,A1-15))/4)

A1=year's service
A2=annual salary