Thanks David

Helped me crack this problem, though to make it work I had to use MIN and MAX
=(5%*MIN(H$5,20000)+6%*MIN(MAX(H$5-20000,0),20000)+7%*MIN(MAX(H$5-40000,0),20000)+8%*MIN(MAX(H$5-60000,0),20000)+9%*MIN(MAX(H$5-80000,0),20000)+10%*MAX(H$5-100000,0))


"David Biddulph" wrote:

"Matt" wrote in message
...
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

Try
=5%*B3+1%*MAX(B3-20000,0)+1%*MAX(B3-40000,0)+1%*MAX(B3-60000,0)+1%*MAX(B3-80000,0)+1%*MAX(B3-100000,0)
--
David Biddulph