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,

 

=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.