Total Gratuity Leave Please help

[Tia]

I am trying to calculate the end of service gratuity.
The Rules are as follows:
Employee:
If the employee resigns under total years of service from 1 till 3
years his gratuity pay will be 7 days for each year
For each working day
If the employee resigns under total years of service from 3 till 5 his
gratuity will be 14 days
If the employee resigns from 5 years till infinity years his gratuity
pay will be 21 days for each year for each
working day
Total day Years Gratuity $ to be paid
150 1 7 1050
150 2 7 2100
150 3 14 6300
150 4 14 8400
150 5 21 15750
150 6 21 18900
150 7 21 22050


If the employers terminate the employee under total years of service
from 1 till 5 years his gratuity pay will be 21 days for each
year for each working day+ Ticket.
from 5 till infinity years his gratuity pay will be 30 days for each
year for each working day+ Ticket.


What I am looking for is a formula for F1 that allows me to get total
gratuity days to pay
Total Per Day Years Gratuity Total To be paid
150 1 21 3,150.00
150 2 21 6,300.00
150 3 21 9,450.00
150 4 21 12,600.00
150 5 21 15,750.00
150 6 30 27,000.00
150 7 30 31,500.00
150 8 30 36,000.00


A1: Employee Name
B1: Total Package per day
C1: Starting Date
D1: Total Years so far
E1: Termination/ Resignation
F1: Total Gratuity days to be paid

 

[Bernard Liengme]

In the sentence "If the employee resigns from 5 years till infinity years
his gratuity
pay will be 21 days for each year for each working day" what does "for each
working day" mean at the end?
Please explain the table at the end of your message. What it 'total 150',
what is 'day'

best wishes

 

[Vijay]

please put this formula in F1.
=IF(E1="r",IF(D1<3,7,IF(D1<5,14,21)),IF(D1<6,21,30))
I persume that in E1 it is the T or R. T for termination and R for
resignation. and in D1 it is number of years of service which is rounded off
to nearest number.

Thanks
Vijay

 

[pogiman via OfficeKB.com]

I suggest you use the LOOKUP function. On another sheet, create a table
similar to this:

Sheet2
Col A Col B
1 0 0
2 1 7
3 3 14
4 5 21

On your original sheet, enter this on F2 (assuming this is the 1st record):

=B1*lookup(D1,Sheet2!$A$1:$B4) -> then copy down

Keep Col A in ascending order for the formula to work properly.

The above formula assumes the rule:
From 0 to <1yr = 0
From >=1yr to <3yrs = 7
From >=3yr to <5yrs = 14
From >=5yr to infinity = 21

The formula need to be modified (entirely maybe?) should the rule be:
From 0 to <1yr = 0
From >=1yr to <=3yrs = 7
From >3yr to <=5yrs = 14
From >5yr and above to infinity = 21

Hope this helps.