Calculation based on numbers of weeks service

[WaqB]

Hi

I need a formula to calculate redundancy in on cell. The current
methodology is

for year 1 you receive 7 weeks pay

Between Year 2 an 10 you receive 4 weeks pay

Between year 11 and 16 year 3 weeks pay

from yr 17 to 25 2 weeks pay, capped at 25 years service.

 

[Sheeloo]

Assuming years of service is in A1, use this in B
=IF(A1<1,0,IF(A1<2,7,IF(A1<11,7+(A1-1)*4,IF(A1<16,43+(A1-10)*3,IF(A1<25,59+(A1-16)*2,79)))))

 

[WaqB]

Hi Sheeloo

Thanks for that it is almost perfect I forgot to put in my question that
those employees that have done more than 1 yrs service but less than 2 will
get additional weeks pay pro rated based on the 4 weeks pay basis.

Can you let me know what I need to include to capture this

 

[Sheeloo]

Tr
=IF(A1<1,0,IF(A1<2,7+(A1-1)*4,IF(A1<11,7+(A1-1)*4,IF(A1<16,43+(A1-10)*3,IF(A1<25,59+(A1-16)*2,79)))))

So for 1.5 years result will be 7 + (0.5*4) i.e. 7 + 2.

Is this what you want ?

 

[Shane Devenshire]

Hi,

Here is another alternative:

=(D1>=1)*7+(D1>=2)*MIN(D1-1,9)*4+(D1>=11)*MIN(D1-10,6)*3+(D1>=17)*MIN(9,D1-16)*2

Note I did not prorate the first year since it is not clear how you want to
handle this - should people with .5 years get a prorate of that *7 or nothing?

Also, I think you need to adjust the prior suggestion to read

=IF(D1<1,0,IF(D1<2,7,IF(D1<11,7+(D1-1)*4,IF(D1<16,43+(D1-10)*3,IF(D1<25,61+(D1-16)*2,79)))))

Where the original had 59 I believe the factor is 61.