if statements
 

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

if statements
 

=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)

"WaqB" wrote in message
...
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

if statements
 

=(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

"Bob Phillips" wrote:

=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)

"WaqB" wrote in message
...
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

if statements
 

Try

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

A1=year's service
A2=annual salary



"WaqB" wrote:

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