multiple sales tax rate

[Nemo]

I am looking for a formula for sales tax, where the tax is based on the amount of the item sold
example - if one item sells for $5000, the first $1600 of the item is taxed at 6.25%, the amount between $1600 & $3200 is taxed at 2.75% and the amount over $3200 is taxed at 1%
Thanks

[Domenic]

Try...

=SUMPRODUCT(--(A1>{0,1600,3200}),A1-{0,1600,3200},{0.0625,-0.035,-0.0175})

Hope this helps!

[Nemo]

Thanks, that worked. It took a couple of minutes to figure out what -0.035,-0.0175 were.
Can I substitute a cell references instead of dollar amounts? i.e. B1 instead of $1600 ?
Also what if the tax rate over $3200 is 0%
Thanks again

[Domenic]

Can I substitute a cell references instead of dollar amounts? i.e. B1 instead of $1600 ?

=SUMPRODUCT(--(A1>B1:B3),A1-B1:B3,{0.0625;-0.035;-0.0175})

...where B1 contains 0, B2 contains 1600, and B3 contains 3200.

Also what if the tax rate over $3200 is 0%
Thanks again

=SUMPRODUCT(--(A1>B1:B3),A1-B1:B3,{0.0625;-0.035;-0.0275})

Also, you may want to have a look at the following link...

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

Hope this helps!

[Nemo]

Got it, the link was really helpfull. The explaination of the how to make a table was just what I needed as I have way too many tax rates to change the formulas each time.
Super Thanks