SOLVED

Excel If Formula

Copper Contributor
I am unable to formulate a formula to help me come up with house allowance using the following details. 
 
If the basic salary is < 30,000 then 23% or 3,000 whichever is higher. If basic salary is > 30,000 but < 50,000 then 20% or 6,900 whichever is higher. And lastly if basic salary is > 50,000 then 15% or 10,000 whichever is higher. 
 
How do I formula an excel formula to help me get the best results? 
6 Replies
best response confirmed by John_Kuria (Copper Contributor)
Solution

@John_Kuria 

With a basic salary in A2:

 

=IF(A2<=30000, MAX(23%*A2, 3000), IF(A2<=50000, MAX(20%*A2, 6900), MAX(15%*A2, 10000)))

 

This can be filled down if required.

@Hans Vogelaar 

The formula has worked perfectly. Thank you very much. 

@John_Kuria 

Hi John,

 

You should use the following formulas:

1st Case - =IF(AND(F2<=30000,(F2*G2)<=3000), 3000, F2*G2) - E2 is 30000 and F2 is 23%

2nd Case - =IF(AND(F3>30000, F3<=50000, (F3*G3)<=6900), 6900, F3*G3) - E3 is (30000 - 50000), F3 is - 20% 

3rd Case - =IF(AND(E3>50000, (E3*F3)<=10000), 10000, E3*F3),  E3 is (>50000), F3 is 15%

 

If you find this solution satisfactory and consider it as a best answer, pelase mention it.

Thanks in advance.

 

 

 

@John_Kuria 

 

Hi John,

 

=IF(AND(E1<=30000,(E1*F1)<=3000), 3000, E1*F1)

=IF(AND(E2>30000, E2<=50000, (E2*F2)<=6900), 6900, E2*F2)

=IF(AND(E3>50000, (E3*F3)<=10000), 10000, E3*F3)

 

Please note attached file.

Good luck.

@Ilgar_Zarbaliyev 

Thank you for going an extra mile in creating a sample excel file. So far this is the best answer I have got. 

 

 

Thank you
1 best response

Accepted Solutions
best response confirmed by John_Kuria (Copper Contributor)
Solution

@John_Kuria 

With a basic salary in A2:

 

=IF(A2<=30000, MAX(23%*A2, 3000), IF(A2<=50000, MAX(20%*A2, 6900), MAX(15%*A2, 10000)))

 

This can be filled down if required.

View solution in original post