ESI Breakup Formula Inquiry
I am using this formula for ESI breakup. Please let me know if an employee's earnings are more than 20,000 Rs, then they are not eligible for ESI. However, if they are absent for around 10 days, their earnings may fall below 15,000 Rs, resulting in automatic ESI deduction. Could you please suggest another formula for this scenario?
=IF(K2<=15001, ROUNDUP(K2*1.75%,0),0)
Regards,
Riyaz.
From India, Bangalore
Acknowledge(0)
Amend(0)
3I am using this formula for ESI breakup. Please let me know if an employee's earnings are more than 20,000 Rs, then they are not eligible for ESI. However, if they are absent for around 10 days, their earnings may fall below 15,000 Rs, resulting in automatic ESI deduction. Could you please suggest another formula for this scenario?
=IF(K2<=15001, ROUNDUP(K2*1.75%,0),0)
Regards,
Riyaz.
From India, Bangalore
Acknowledge(0)Amend(0)
ESI Deduction Formula Clarification
For ESI deduction, the IF condition should be used in the Actual salary, not in the Earned salary. If you use the formula in the Actual salary, you mentioned that mistakes will not happen.
Example for ESI Deduction Formula
- **Actual Salary:**
G4 = Gross Salary (20,000)
- **Earned Salary:**
H4 = Gross Salary (13,000)
Then, the formula would be as follows:
=IF(G4<=15000, H4*1.75%, 0)
If you use this formula, you mentioned that mistakes will never happen.
Thanks & Regards,
A. Maria Jerome
From India, Chennai
Acknowledge(0)Amend(0)
For ESI deduction, the IF condition should be used in the Actual salary, not in the Earned salary. If you use the formula in the Actual salary, you mentioned that mistakes will not happen.
Example for ESI Deduction Formula
- **Actual Salary:**
G4 = Gross Salary (20,000)
- **Earned Salary:**
H4 = Gross Salary (13,000)
Then, the formula would be as follows:
=IF(G4<=15000, H4*1.75%, 0)
If you use this formula, you mentioned that mistakes will never happen.
Thanks & Regards,
A. Maria Jerome
From India, Chennai
Acknowledge(0)Amend(0)
Dear Basha,
A. Maria Jerome is right. If you use this formula to calculate ESI, it will give you the wrong output because the comparison your formula is making is on Gross Earnings. Therefore, it is better to keep the Net Salary part also in the Excel sheet and put the formula to evaluate the net salary. On this basis, it should calculate ESI from the earned salary.
The above-suggested formula will solve your problem, but in some cases, it may calculate Rs. 1 less. Please check the attachment. The formula is a little complicated but will give you the exact contribution amount.
Hope this will help you.
From India, Delhi
Acknowledge(0)Amend(0)
A. Maria Jerome is right. If you use this formula to calculate ESI, it will give you the wrong output because the comparison your formula is making is on Gross Earnings. Therefore, it is better to keep the Net Salary part also in the Excel sheet and put the formula to evaluate the net salary. On this basis, it should calculate ESI from the earned salary.
The above-suggested formula will solve your problem, but in some cases, it may calculate Rs. 1 less. Please check the attachment. The formula is a little complicated but will give you the exact contribution amount.
Hope this will help you.
From India, Delhi
Acknowledge(0)Amend(0)CiteHR is an AI-augmented HR knowledge and collaboration platform, enabling HR professionals to solve real-world challenges, validate decisions, and stay ahead through collective intelligence and machine-enhanced guidance. Join Our Platform.
SALARY SHEET CALCULATION.xls
Salary calculation.doc