Calculation based on numbers of weeks service
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.
--
If this helps, please click the Yes button
Cheers,
Shane Devenshire
"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.
|