Oct 06 2020 07:08 AM
Oct 06 2020 07:27 AM
SolutionWith 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.
Oct 06 2020 07:35 AM
The formula has worked perfectly. Thank you very much.
Oct 06 2020 07:41 AM
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.
Oct 06 2020 07:44 AM
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.
Oct 06 2020 11:07 PM
Thank you for going an extra mile in creating a sample excel file. So far this is the best answer I have got.
Oct 06 2020 07:27 AM
SolutionWith 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.