Shorter formula:

=SUM(((B3/10000-{0,2,4,6,8,10})0)*(B3/10000-{0,2,4,6,8,10})*10000*({5,1,1,1,1,1}/100))
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

After reading the other responses, I KNEW I must have mis-read the post.

Here's a better formula:

For a taxable value in B3

The tax calculation would be:
=SUMPRODUCT(IF((B3/10000-{0,2,4,6,8,10})0,((B3/10000-{0,2,4,6,8,10})*10000)*{5,1,1,1,1,1}/100,0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Perhaps something like this:

With a value in A1

The tax rate for A1 is:
B1: =MIN(4%+CEILING(A1/20000,1)/100,10%)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Matt" wrote:

Hello Everyone

I'm trying to come up with an efficient formulae or function to calculate tax
The problem I have is that the tax is progressive. As below
the first 20,000 is taxed at 5%
the next 20,000 is taxed at 6%
the next 20,000 is taxed at 7%
the next 20,000 is taxed at 8%
the next 20,000 is taxed at 9%
more than 100,000 is taxed at 10%

I'm trying to do a formula like below
Cell B3 is my taxable amount
Cell B5 =IF(B$320000,20000*0.05,B$3*0.05)
Cell B6 =IF(B$340000,40000*0.05,(B$3-20000)*0.05)

This gives me a problem in that for 35k say, I end up with a negative number
for the second part in cell B6
My other issue is that each calculation will take up 6 rows on my
spreadsheet. I was hoping to set up a function that could do this in a cell,
but even the simple stage defeats me at the moment.

Thanks for reading this far and any help would be greatly appreciated

Thank you