 SOLVED

# Excel If Formula

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 (New Contributor)
Solution

# Re: Excel If Formula

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.

# Re: Excel If Formula

The formula has worked perfectly. Thank you very much.

# Re: Excel If Formula

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.

# Re: Excel If Formula

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.

# Re: Excel If Formula

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