If(and(...,....) with multiple conditions (1 Viewer)

Ashfaque

Student
Local time
Today, 07:05
Joined
Sep 6, 2004
Messages
894
Hi,

I am trying to generate a formula using IF (AND(.....,....),) in attached excel sheet as per requirement to calculate End of Service Benefit as per attached %age and relevant service period.

I can calculate accurately till 5 years in both situation i.e. Resignation or Termination by company.

But as the years of service increases and the reason of leaving job changes (Resignation or Termination, it becomes difficult to write IF(AND(...) with multiple conditions in the formula. Because there are 4 conditions to check at one time.

I have attached the excel sheet and the formula of EOSB calculation. At the moment we based on Basic Salary only.

The side table cells which I have highlighted with Orange colors are containing the formulas I am trying to get in but it looks it is messed up somewere.

To ge the correct amount of EOSB, we can use calculator provided by KSA Govt the link of which I have placed here but I want this calculations to be on Excel sheet so that any employee can easily understand what he is receiving at the end of his service.

https://laboreducation.gov.sa/En/Pages/Calculator.aspx

1. The type of contract shall be 'Unlimited' from the first box on this portal
2. End of service reason : Resignation by Worker

Then this generates the correct amount which I planned to bring it on Excel sheet. Kindly correct the formulas in Yellow cells only to get the correct amount as displaying on https://laboreducation.gov.sa/En/Pages/Calculator.aspx

:banghead: Please extend your help....
 

Attachments

  • LATEST EOSB - ACCOUNTS.zip
    82.9 KB · Views: 117

June7

AWF VIP
Local time
Yesterday, 17:35
Joined
Mar 9, 2014
Messages
5,423
Might get more response if you post to an Excel forum.

I would build VBA custom function to do this calc.
 

Users who are viewing this thread

Top Bottom