Forum Discussion

John_Kuria's avatar
John_Kuria
Copper Contributor
Oct 06, 2020
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 ...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Oct 06, 2020

    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.

Ilgar_Zarbaliyev's avatar
Ilgar_Zarbaliyev
MCT
Oct 06, 2020

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's avatar
    John_Kuria
    Copper Contributor
    Oct 06, 2020

    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.