SOLVED

Excel If Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1749419%22%20slang%3D%22en-US%22%3EExcel%20If%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1749419%22%20slang%3D%22en-US%22%3E%3CDIV%3EI%20am%20unable%20to%20formulate%20a%20formula%20to%20help%20me%20come%20up%20with%20house%20allowance%20using%20the%20following%20details.%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EIf%20the%20basic%20salary%20is%20%26lt%3B%2030%2C000%20then%2023%25%20or%203%2C000%20whichever%20is%20higher.%20If%20basic%20salary%20is%20%26gt%3B%2030%2C000%20but%20%26lt%3B%2050%2C000%20then%2020%25%20or%206%2C900%20whichever%20is%20higher.%20And%20lastly%20if%20basic%20salary%20is%20%26gt%3B%2050%2C000%20then%2015%25%20or%2010%2C000%20whichever%20is%20higher.%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EHow%20do%20I%20formula%20an%20excel%20formula%20to%20help%20me%20get%20the%20best%20results%3F%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1749419%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1749501%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1749501%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F822002%22%20target%3D%22_blank%22%3E%40John_Kuria%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20a%20basic%20salary%20in%20A2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(A2%26lt%3B%3D30000%2C%20MAX(23%25*A2%2C%203000)%2C%20IF(A2%26lt%3B%3D50000%2C%20MAX(20%25*A2%2C%206900)%2C%20MAX(15%25*A2%2C%2010000)))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20can%20be%20filled%20down%20if%20required.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1749523%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1749523%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20has%20worked%20perfectly.%20Thank%20you%20very%20much.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1749530%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1749530%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F822002%22%20target%3D%22_blank%22%3E%40John_Kuria%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20John%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(E1%26lt%3B%3D30000%2C(E1*F1)%26lt%3B%3D3000)%2C%203000%2C%20E1*F1)%3C%2FP%3E%3CP%3E%3DIF(AND(E2%26gt%3B30000%2C%20E2%26lt%3B%3D50000%2C%20(E2*F2)%26lt%3B%3D6900)%2C%206900%2C%20E2*F2)%3C%2FP%3E%3CP%3E%3DIF(AND(E3%26gt%3B50000%2C%20(E3*F3)%26lt%3B%3D10000)%2C%2010000%2C%20E3*F3)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20attached%20file.%3C%2FP%3E%3CP%3EGood%20luck.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1752370%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1752370%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F481821%22%20target%3D%22_blank%22%3E%40Edgar_Martin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20going%20an%20extra%20mile%20in%20creating%20a%20sample%20excel%20file.%20So%20far%20this%20is%20the%20best%20answer%20I%20have%20got.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1752391%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20If%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1752391%22%20slang%3D%22en-US%22%3EThank%20you%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted
Best Response confirmed by John_Kuria (New 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.

Highlighted

@Hans Vogelaar 

The formula has worked perfectly. Thank you very much. 

Highlighted

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

 

 

 

Highlighted

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

Highlighted

@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