vlookup or if statement

[Guest]

hey i really need help with a formula because i don't know which one to use.
i have this information

0-10000 = O% tax
10000-90000 = 20% tax
90001-100000= 30% tax

and i have a lot of values for it but i dont know how to make it like simple
to work out as one formula like the IF statement can be used if it only had
to variables but it has three and i dont know what formula to use.
please help its really important

thank you heaps.

 

[Bernard Liengme]

a) Using IF: =IF(A1>90000,30%,IF(A1>10000,20%,0%))
but you are limited to 7 level of nesting with Excel version prior to XL2007
b) With LOOKUP: =LOOKUP(A1,{0,10001,90001},{0,0.2,0.3})

best wishes

 

[Arvi Laanemets]

Hi

=(0.2*(A2>10000)+0.1*(A2>90000))*A2

 

[David Biddulph]

You may need to clarify what you want. Are you looking for the tax rate, or
the tax payable? If the latter, presumably the rate is applicable on the
amount within that band?
If when you say "i have a lot of values for it" you mean that there are a
large number of additional steps beyond the 30% band, then you may be better
with a lookup.

Tax rate: =IF(A1<=10000,0,IF(A1<=90000,20%,IF(A1<100000,30%,"undefined")))
Tax payable:
=IF(A1>100000,"undefined",30%*(MIN(10000,MAX(A1-90000,0)))+20%*(MIN(80000,MAX(A1-10000,0))))

 

[Guest]

See:

http://www.mcgimpsey.com/excel/variablerate.html