If Statement setting with minimum criteria

Question

With the if statement i am able to create a criteria of reduction as per slab, but in same formula I cannot insert logic of minimum cap. Can you help me with setting up minimum cap formula as well.

(IF(L4>60000,L4*70%,IF(L4>40000,L4*75%,IF(L4>20000,L4*80%,IF(L4<20000,L4*90%,0))))/I4)*K4

using the abve formula I can set reduction criteria but not the minimum cap.

Under 20k - Reduction by 10% of CTC with minimum cap of 10,000

Above 20k and upto 40k - Reduction by 20% of CTC with minimum cap of 20,000

Above 40k and upto 60k - Reduction by 25% of CTC with minimum cap of Rs. 32,000

60k plus and above - Reduction by 30% of CTC with minimum cap of Rs. 45,000

Answer 1

Hi

My name is André. I am an independent consultant.

>I couldn't quite understand exactly what this CAP is. however you can use the AND function, to add a condition to your if.

For example:

if(and(L4<2000,L4>10000),L4*90%,0)

But there are other ways to do it, I just need to understand the desired function of the CAP a little better.

Answer here so I can continue helping you.

André.

Answer 2

Hi MurtazaBurhani

Please, try the following steps.

1. Create a table similar to the one in the below picture anywhere on the workbook. Commonly we choose a new sheet or an existed Reference/Index sheet

And give it a Named Range "CAPtable" or choose a name of your preference.

****************************************************************************************

EDITED:

Please refer to my next reply

2) I partially reproduced your scenario to better illustrate how the formula works and the expected results.

If I correctly understood the CAP meaning, then column N's formula is the one you are looking for.

=IF(K4=0,0,MAX($L4*$K4/$I4*VLOOKUP($L4,CAPtable,2,TRUE),VLOOKUP($L4,CAPtable,3,TRUE)))~~~~

Note:

Adapt the ranges in the formula according to your scenario.

![Image](https://learn-attachment.microsoft.com/api/attachments/d73dbbb6-1cc0-4745-8ec2-1f28c981ed8e?platform=QnA

Answer 3

After a deeper analysis of your scenario.

Here a few thoughts

1. I'm joining our colleague Minhokiller in his comments, although I do understand the meaning of the CAP, I think we need to know, how you implement it, so please clarify it?
2. I'm obviously not familiar with the payroll basis/payment methods/policies/rules and work entitlements in your country

I just simply follow my logic in this case.

If the BASIC Salary (column L) represents the basic monthly salary for an employee.

According to your formula calculations/logic, none of the employees will ever get their full BASIC Salary before any tax (%) deductions.

Employees will have to work every day without having any day-off (commonly weekends) in order to get their full BASIC salary payment

Example.

The month of June 2021 had

22 working days (Monday to Friday) and 8 days off (Saturday-Sunday)/(weekends)

In the picture:

Employees SI No. 1, 4, 7, and 8, worked 24 days in the month, they should be entitled to get their full BASIC monthly salary plus two extra days of their salary worth. (obviously before any tax % deductions)

So to calculate the employee Daily Salary the logic should be,

BASIC Monthly salary multiplied by Year Months (12) divided by Total working days in a calendar year

The Total working days in a calendar year varies in every country depending on their customs and established rules and public holidays.

In my humble opinion here is a simplified sample of the calculations according to your scenario.

Payment (before deductions and CAP) =$K4*$L4*12/252

Payment (after % deductions) =$M4*VLOOKUP($L4,CAPtable,2,TRUE)

Again

Please, clarify how the CAP works so we could provide you with the full answer to your question

Regards

Jeovany

Answer 4

Due to pandemic the company is implementing Voluntery paycut

In this scenario,

Employees having salary Under 20k - Deduction by 10% of CTC with a minimum cap of 10,000

Employees having salary between 20k and 40k - Deduction by 20% of CTC with a minimum cap of 20,000

Employees having salary between 40k and 60k - Deduction by 25% of CTC with a minimum cap of Rs. 32,000

Employees having salary between 60k plus and above - Deduction by 30% of CTC with a minimum cap of Rs. 45,000

The minimum cap is they would get this minimum salary even if the deduction criteria is less than the minimum cap.

So I am looking for a standard formula that can help me calculating salary after the above mentioning condition instead of manually calculating for each individual. I hope this clarifies my requirement.

Answer 5

Due to pandemic the company is implementing Voluntery paycut

In this scenario,

Employees having salary Under 20k - Deduction by 10% of CTC with a minimum cap of 10,000

Employees having salary between 20k and 40k - Deduction by 20% of CTC with a minimum cap of 20,000

Employees having salary between 40k and 60k - Deduction by 25% of CTC with a minimum cap of Rs. 32,000

Employees having salary between 60k plus and above - Deduction by 30% of CTC with a minimum cap of Rs. 45,000

The minimum cap is they would get this minimum salary even if the deduction criteria is less than the minimum cap.

So I am looking for a standard formula that can help me calculating salary after the above mentioning condition instead of manually calculating for each individual. I hope this clarifies my requirement.