Formula Required

[abid.aries]

Respected Members,

I need a universal to calculate leaves of our employees. The calculation will be made on as follow.

1. If an employee has worked for the company for more than 5 years or less then 5 years then his annual leaves calculation would be like this
   5 and Above year 30 Leaves
   4.99 and 3.0 Year 21 Leaves
   2.99 and 1.0 Year 14 Leaves
   Less then 1.0 year will be treated as 14 annual leaves

for working I am attaching a sample data file

 

[Ashhu]

hi
Put this formula in cell and drag. uploaded the file.
=IF(G5>=5,"30",IF(G5>=3,"21",IF(G5>=1,"14","14")))

i have one question , is below statement is right. Both says 14 leaves.
2.99 and 1.0 Year 14 Leaves
Less then 1.0 year will be treated as 14 annual leaves

 

[Naresh Yadav]

pls try this one

=LOOKUP(G5,{0,1,3,5},{14,14,21,30})

 

[abid.aries]

hi
Put this formula in cell and drag. uploaded the file.
=IF(G5>=5,"30",IF(G5>=3,"21",IF(G5>=1,"14","14")))

i have one question , is below statement is right. Both says 14 leaves.
2.99 and 1.0 Year 14 Leaves
Less then 1.0 year will be treated as 14 annual leaves

Dear Ashhu,

Less than 1.0 year will be treated as 0 annual leaves

 

[Ashhu]

Dear Ashhu,

Less than 1.0 year will be treated as 0 annual leaves

updated formula, both works

=LOOKUP(G5,{0,1,3,5},{0,14,21,30})
or
=IF(G5>=5,"30",IF(G5>=3,"21",IF(G5>=1,"14","0")))

 

[Khalid NGO]

Hi,

Another option with Choose / Match:

=CHOOSE(MATCH(G5,{0,3,5},1),14,21,30)

Regards,

 

[abid.aries]

Hi,

Another option with Choose / Match:

=CHOOSE(MATCH(G5,{0,3,5},1),14,21,30)

Regards,

Thanks, Khalid NGO,

This formula save my life.

 

[bosco_yip]

An unusual way, in I5 enter :

=IF(G5>1,(G5>=5)-(G5<=3),"a")

and also in I5 >> Custom Cells Format >> in the Type box enter :

3\0;14;21;\0

all copy down

Regards
Bosco

 

[Khalid NGO]

@bosco_yip claver use custom formatting

 

[bosco_yip]

@bosco_yip claver use custom formatting

Thank you, Khalid NGO,

It is recommend to use LOOKUP() in this example.

Using CF in this example is not recommended and just for fun.

Reasons are :

1] The CF is not actual figure and unable to use for further calculation.

2] The CF have some restrictions, in this example :

It can not use 30;14;21;\0

Excel will change it to 31 while the result is Positive ( 0 auto change to 1)

That is why I am using 3\0;14;21;\0 -->> to force it become 30

Regards
Bosco