Calculation based on numbers of weeks service
Try
=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 ?
"WaqB" wrote:
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" wrote:
Assuming years of service is in A1, use this in B1
=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)))))
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.
"WaqB" wrote:
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.
|