tax table using IF,

[campana82]

I'm trying to create a formula that mimicks the California PIT tax table, idea being that I enter the payroll amount and it computes the taxes that I owe. This is what I have so far
=IF((A15-E17*83)<2496,((A15-E17*83)-1052)*0.02+10.52,IF(3940>(A15-E17*83)>2497,((A15-E17*83)-2496)*0.04+39.4)

a15=payroll amount
e17=amount of deductions

The actual tables are located here
http://www.edd.ca.gov/taxrep/06methb.pdf
page 9 monthly/married

I've been using If statements to throw the payroll number into the correct calculations, is there an easier way to go about this? Can I even include that many If statements, or should I create a seperate table with the calculations and just point the correct payroll amount to that? any help would be appreciated.

 

[barry houdini]

Try this method here......

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

for your case that would look something like this

Book1
	A	B	C	D	E	F
1	lower threshold	%		pay after deductions	PIT
2
3	$0.00	1%		$8,000	$405.54
4	$1,052.00	2%
5	$2,496.00	4%
6	$3,940.00	6%
7	$5,468.00	8%
8	$6,912.00	9.30%
9	$83,334.00	10.30%
10
11
12
13
14	payroll amount
15	$10,000.00
16					deductions
17					$2,000.00
18
Sheet3

where D3 contains the taxable balance (A15-E17) and formula in E3 is

=SUMPRODUCT(--(D3>A$3:A$9),(D3-A$3:A$9),B$3:B$9-B$2:B$8)

note B2 must be blank or zero