if and functions to compute Professional Tax

[excelnovice2000]

Hello Gurus,
Can I get some help in solving excel problem using if and function. I have attached a sample file

3 Conditions:
If amount is less than 15000 value is 0

If amount is >= 15000 and <= 20000 value is 150

If amount is > 20001 value is 200

I tried the below formula, but I am getting FALSE values


IF(A3<15000,0,IF(AND(A3>=15001,A3<=20000,150),IF(A3>20000,200)))


Thanks for all your help

 

[Marcilio]

excelnovice2000, Good morning.

Try to use: IF(A3<15000, 0, IF(AND(A3>=15000,A3<=20000), 150, 200))

Please, tell us if it's what you need.

I hope it helps.

 

[vletm]

excelnovice2000
for 3rd row =IF(A3<15000,0,IF(A3>20001,200,150))
... but case 20001?

 

[bosco_yip]

Or,

In B3, formula copied down :

=LOOKUP(A3,{0;15001;20001},{0;150;200})

 

[excelnovice2000]

excelnovice2000, Good morning.

Try to use: IF(A3<15000, 0, IF(AND(A3>=15000,A3<=20000), 150, 200))

Please, tell us if it's what you need.

I hope it helps.

Thanks Marcilio. That worked. Thanks for your help

 

[excelnovice2000]

excelnovice2000
for 3rd row =IF(A3<15000,0,IF(A3>20001,200,150))
... but case 20001?

Thanks Vletm.

 

[excelnovice2000]

Or,

In B3, formula copied down :

=LOOKUP(A3,{0;15001;20001},{0;150;200})

Thanks Bosco. That worked.

 

[Marcilio]

excelnovice2000, Good morning.

You're welcome.

Thanks for the feedback and Glad to help you.