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.
From India, Bangalore
3Iam 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.
From India, Bangalore
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
From India, Chennai
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
From India, Chennai
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.
From India, Delhi
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.
From India, Delhi
Community Support and Knowledge-base on business, career and organisational prospects and issues - Register and Log In to CiteHR and post your query, download formats and be part of a fostered community of professionals.
ESI contribution rates lowering.pdf