Regarding Esi ..... - Xls Download

Dear All ,
Iam using this formula for ESI break up , pls let me if employee Earning is more than 20,000rs then he is not applicable for ESI , but if he is absent for some 10days , then his earning come down below 15,000rs,,, automatic Esi is deducting him .. So pls say me other formula for this ......
=IF(K2<=15001,ROUNDUP(K2*1.75%,0),0)
regards
riyaz.

Dear Basha,
For ESI deduction, IF condition we should use in Actual salary not in earned salary. If you use formula in Actual salary you said mistake will not happen.
For Example :
Actual Salary
G4 = Gross Salary ( 20000)
Earned Salry
H4 = Gross Salary (13000)
then,
formula would come like this,
=IF(G4<=15000,H4*1.75%,0)
you use this formula, you said mistake will never happen.
Thanks & Regards
A.Maria Jerome

Dear Basha,
A.Maria Jerome is right. If you will place this formula to calculate ESI, then it will give you wrong output, as the comparison your formula is making on Gross Earning.
So, it is better to keep the Net Salary Part also in the excel sheet and put the formula to evaluate the net salary, on its basis it should calculate ESI from earned salary.
The above suggested formula will solve your problem, but in some cases it will calculate Rs. 1 less.
So, check the attached. The formula is little complicated but will give you the exact contribution amount.
Hope this will help you.

Dear All,
Please find the ESIC Calculation formula :-
=IF(G5<=15001,ROUNDUP(G5*1.75%,0),0)
(whether G2 is Total Gross Income of Employee)