Functioins don't want to "function"
I’m creating an invoice for large ticket items that requires the application of three levels of taxes on the subtotal. Tax 1 is on the total amount of the item at .07. Tax 2 is on the first $1,600 at .0225. Tax 3 is on the remainder of the subtotal after subtracting the first $1,600 but, on no more than $3,200. I need to write these in a way that the entirety of the tax will show in a single cell.
So far, this is what I have:
Tax 1 – =SUM (C12*.07) (This works)
Tax 2 – =IF (C12>=1600, 1600*.0225) (This works. It still works when added to the first function.)
Tax 3 – bupkus
Apparently, I don’t understand the system enough to write a complicated function to handle the math. I’m now facing a deadline and have spent more than a week trying to find something that would work. Can anyone help?
=(C12*.07)+IF(C12>=1600, 1600*.0225, C12 * .0225)+IF(C12 > 3200, 1600 * 0.01, IF(C12 < 1600, 0, (C12-1600)* 0.01))
1. Tax of 0.07 on the sales price.
2. Tax of 0.0225 on the first $1,600 of that amount.
3. Tax of 0.0275 on any amount up to $3,200 of the sales price.
I think you absolutely nailed it. Need to go root through all of the balled up paper to find where I messed up. Thanks so much.
3a. Tax of 0.0275 on any amount from $1600.01 to $3,200 of the sales price.
and not:
3. Tax of 0.0275 on any amount up to $3,200 of the sales price.
The statement provided utilizes 3a and not 3 – as stated in your original question.
Tax 1: entire amount taxed at .07%
Tax 2: first 1600 taxed at additional 0.0225%
Tax 3: second 1600.01 taxed at additional 0.0275% (up to 3200) and no more than $44 total on the third tax.
I’m lost.
Nothing to change.
EDIT:
This should work for Tax 3 ( used .01 rate as example since you didn’t specify)
Also I believe your Tax 2 should read:
You accounted for > 1600 but not less than.