if statement

  • Thread starter Thread starter Andy Copeland
  • Start date Start date
A

Andy Copeland

Hi,
I am trying to use an if statement to calculate tax rates for withholding in
Excel 2000. It works for the first clause (110 to 404), but not the others
and I get an error when I try to add the last if statement for the single
classification. Why won't this work? Is there a better (one that works)
way of doing this? The spreadsheet has columns for name, marital status,
hours, wages, withholding, social security, medicare and paycheck.
Thanks,
Andy


=IF(C4="S",IF(AND(F4>110,F4<404),((F4-110)*0.1)),IF(C4="S",IF(AND(F4>404,F4<
1283),((F4-404)*0.15+29.4)),IF(C4="S",IF(AND(F4>1283,F4<2854),((F4-1283)*0.2
5+161.25),IF(C4="S",IF(AND(F4>2854,F4<6196),(F4-2854)*0.28+554),IF(C4="S",IF
(AND(F4>6195,F4<13383),((F4-6196)*0.33+1489.76))))))))
 
Hi
Excel limits the depth of nested functions to a maximum of seven (which
you have reached). If would suggest using a lookup table and
VLOOKUP/MATCH to get your tax rate. To giv you an example: Create a
separate sheets (lets call this 'lookup') with the following layout
A B C
1 rate add
2 110 0.1 0.0
3 404 0.15 29.4
4 1283 0.25 161.25
.....

Now you may use the following formula:
=IF(C4="S",(F4-INDEX('lookup'!$A$2:$A$20,MATCH(F4,'lookup'!$A$2:$A$20,1
),1))*INDEX('lookup'!$A$2:$A$20,MATCH(F4,'lookup'!$B$2:$B$20,1),1)+INDE
X('lookup'!$A$2:$A$20,MATCH(F4,'lookup'!$C$2:$C$20,1),1),"other C4
value")
 
Back
Top