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

[Ashfaque Hussain]

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.

(attached pic of eosb calculator portal)

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 the EOSB portal.

Please extend your help....

[Ashfaque Hussain]

Is there somebody to help out on this Excel formula issue?

[JeteMc]

If I understand correctly the ESB is based on the classifications 'Resignation' and 'Termination'. It would seem that you could classify each of the categories (K23:K34) into one of those two classifications using the corresponding empty cells in the column J. Then once a category has been chosen in cell D13, cell J13 would populate using a lookup to the categories in column K. The formulas would then reference cell J13 as opposed to D13.
Let us know if you have any questions.