Help for UAE Gratuity Formula

[binoyfalcon]

Can anyone help me the formula for Gratuity Calculation, Please.....

Basic Salary 1000.00


Employed for less than 1 year - no gratuity

Employed between 1-3 years - 7 days for each year of employment

Employed between 3-5 years - 14 days for each year of employment (

Employed longer than 5 years - 21 days for each year up to 5 years,

and 30 days for each year after 5 years

 

[SirJB7]

Hi, binoyfalcon!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


Normal way:

a) Place this table anywhere:

-----

[pre]

Years	Gratuity
0	0
1	7
3	14
5	21
10	30

[/pre]
-----

b) Use this formula:

=VLOOKUP(<cell for years>,<range of previous table>,2,TRUE)


Short but weird way:

a) Use this formula:

=ELEGIR(COINCIDIR(D1;{0;1;3;5;10};1);0;7;14;21;30) -----> in english: =CHOOSE(MATCH(D1,{0;1;3;5;10},1),0,7,14,21,30)


Regards!

 

[Sajan]

Hello SirJB7,

I think the OP wants the gratuity calculated in terms of the salary amount. As such, for a base salary of $1000, I think the formula needs to be something like:

=(1000/365)*((K5>5)*(K5-5)*30 +INDEX({0,7,14,21}, MATCH(K5, {0,1,3.1,5}))*MIN(K5,5))


where K5 is the number of years worked

and assuming a 365 day year


Sample data:

[pre]

Years	Days	Gratuity
1	7	19.17808219
2	14	38.35616438
3	21	57.53424658
4	56	153.4246575
5	105	287.6712329
6	135	369.8630137
7	165	452.0547945
8	195	534.2465753
9	225	616.4383562
10	255	698.630137
11	285	780.8219178
12	315	863.0136986
13	345	945.2054795
14	375	1027.39726
15	405	1109.589041

[/pre]
Cheers,

Sajan.

 

[NARAYANK991]

Hi ,


An alternative :


=Basic_Salary*IF(Employed_Years<1,0,IF(Employed_Years<3,7*Employed_Years,IF(Employed_Years<5,14*Employed_Years,21*5+30*(Employed_Years-5))))/365


or what would be my personal preference , use a Lookup Table as suggested by SirJB7 ; suppose we call it Gratuity Table , and structure it as follows :

[pre]

Years	Gratuity
0          0
1          7
3         14
5         21
6         30

[/pre]
The formula now becomes :


=IF(Employed_Years>Years_Limit,Basic_Salary*VLOOKUP(Years_Limit,Gratuity_Table,2,TRUE)*Years_Limit+Basic_Salary*(Employed_Years-Years_Limit)*VLOOKUP(Employed_Years,Gratuity_Table,2,TRUE),Basic_Salary*Employed_Years*VLOOKUP(Employed_Years,Gratuity_Table,2,TRUE))/365


where Years_Limit is 5.


Narayan

 

[SirJB7]

@Sajan

Hi!

I understood that as if they were vacation or permission days since that scale corresponds exactly (except for last step, 28 vs. 30) with certain union agreements in my country. Thanks for the catch.

Regards!

 

[binoyfalcon]

Thank you dear Friends.........

 

[BIJU JOSEPH]

Please let me know the formula for -



Basic Salary 1000.00


Employed for less than 1 year - no gratuity

Employed between 1-3 years - 7 days for each year of employment

Employed between 3-5 years - 14 days for each year of employment (

Employed longer than 5 years - 21 days for each year up to 5 years,

and 30 days for each year after 5 years