[Solved] Three options for one cell, may be an IF statement

[DOSman]

I wish to set up a spreadsheet to calculate tax. The tax rates are
$18,201-37,000 19.0% of income over $18,200
$37,001-87,000 $3,572 plus 32.5% of income over $37,000.
$87,001-180,000 $19,822 plus 37.0% of income over $87,000

Income is put in cell B7. The best function that I can find is IF, but it's not working. The cell will have three parts
If income is $18,200 to $37,000, tax is 19.0% of income over $18,000
If income is $37,001-87,000, tax is $3,572 plus 32.5% of income over $37,000
If income is $87,001-180,000, tax is $19,822 plus 37.0% of income over $87,000

I have a formula which does not work for just one of the above, let alone three:
=IF(18200<B7<37000);0.19*(B7-18200)
This is giving error 509, missing operator.

I'm reasonably comfortable with basic spreadsheets but have never delved into this area before. Can someone please advise me of the best way to set up the cell? TIA.

[RoryOF]

Your IF syntax should be
IF(Test;Then_Value;Otherwise_Value)

Count your brackets. Where is the outer enclosing pair of brackets? Where is the Otherwise value?

[Lupp]

In spreadsheets a condition must not contain a double inequation. You would have to use the AND function for the two comparisons.
This would result in

Code: Select all

=IF(AND(18200<B7;B7<=37000);0.19*(B7-18200);0)

as far as the first interval of the table is concerned (The "=" added for accuracy). But this will not be what you actually need. The tax will be accumulative in the sensethat an amount for the lower part of the income is alo to pay if the total income is higher. The word "plus" occurring twice in your description is telling that (and all the stepping tax systems I heard of do it this way). Thus the correct share to the total tax based on the first relevant interval is simply

Code: Select all

=Max(0;Min(B7;37000)-18200)*0.19

and

Code: Select all

=MAX(0;MIN($B7;37000)-18200)*0.19+MAX(0;MIN($B7;87000)-37000)*0.325+MAX(0;$B7-87000)*0.37

should get it all.
See also the attached example also containing a solution based on a lookup table (though without VLOOKUP).
No guarantee of any kind!

[FJCC]

Here is a method based on VLOOKUP()

[DOSman]

Thanks. I counted the brackets and they match. There may be a need for another set, but as i said, I am new to IF functions. Lupp, that worked very well. Danke. I will now attempt to analyse how it worked..

[RoryOF]

It was missing enclosing brackets and the Otherwise_Value).