Subtracting and multiplying tax numbers witht he IF function

[Guest]

Ok here is what i have i have created a payroll. now i need to add this:

if pay is $0 - $95 deduct $0 plus 0c per dollar over 0
if pay is $96-$345 deduct $0 plus 20c per dollar over 96
if pay is $346-$480 deduct $63 plus 25c per dollar over 346
if pay is $481-$672 deduct $96 plus 40c per dollar over 481
if pay is $673-$961 deduct $183 plus 47c per dollar over 673
if pay is $962+ deduct $308 plus 48c per dollar over 962

And make it automatically deduct from the Gross Income.
I have tried this
=IF(C4>300,C4<400,(C4-43)*0.36,IF(C4>401,C4<500,(C4-60)*0.45,IF(C4>501,<600,(C4-200)*0.60)))

(numbers were just to try out)
and it didnt work.
How would i go about creating such a formula? or is there a different way of
handling it?

 

[Sandy Mann]

I made a table from your conditions as follows:

D16 contains: 0, 95, 345, 480, 672, 961
E1:E6 contains: 0, 0, 63, 96, 183, 308
F1:F6 contains: 0, 0.2 0.25, 0.4, 0.47, 0.47

Then with the Pay in A1 enter the formula:

=A1-(VLOOKUP(A1,D1:F6,2)+(A1-VLOOKUP(A1,D1:F6,1))*VLOOKUP(A1,D1:F6,3))

in B1 to get the pay after tax.

HTH

Sandy

 

[JE McGimpsey]

Take a look here:

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