Tax Computation

Hi Experts,

I need it in a best way to calculate the professional tax for few states as attached. I tried using “IF” condition to do, but in one cell I am able to use only 7 conditions of IF, where as I require more than that.

Here is the one of the condition I used to calculate (cells in this formula doesnot belongs the sheet attached)
=IF(AND(($Z$29-$BT$23)>=-15000,($Z$29-$BT$23)<=1500),0,IF(AND(($Z$29-$BT$23)>=1501,($Z$29-$BT$23)<=2000),16,IF(AND(($Z$29-$BT$23)>=2001,($Z$29-$BT$23)<=3000),25,IF(AND(($Z$29-$BT$23)>=3001,($Z$29-$BT$23)<=4000),35,IF(AND(($Z$29-$BT$23)>=4001,($Z$29-$BT$23)<=5000),45,IF(AND(($Z$29-$BT$23)>=5001,($Z$29-$BT$23)<=6000),60,IF(AND(($Z$29-$BT$23)>=6001,($Z$29-$BT$23)<=10000),80,100)))))))

In the attached sheet, if state refers to Andhra Pradesh (C24) and the value is between the amount specified of the Andra Pradesh table, PT Dedcutable value should be calculated automatically.

Can any Macro or any possibility to do it in a best way.

Thanks in advance

Regards,
Harish S

Harish,

Give this a try and see if it is doing what you want. I added a button that you can click once you have entered your numbers and it will find the correct amount.

This is fairly "hard" coded so the state name you enter has got to be exactly as you have it listed in your Min Max sections otherwise you will get a message box telling you the state has not been found.

Hope this helps!

Harish,

It was my pleasure and I'm glad it worked for you.