From: MZ on 22 Apr 2010 06:05 A formula tests for three ranges of income levels for five filing statuses; depending upon the results, the taxpayer will receive a tax deduction for \$4,000, \$2,000, or \$0 (i.e., no deduction) The table of ranges of income, filing statuses, and deduction ceilings are as follows: Legend: M-FJ (Married filing jointly) M-FS (Married filing separately) S (Single) HOH (Head of Household) QW (Qualifying Widower) M-FJ S/HOH/QW Deduction amount \$130,000 \$65,000 \$4,000 \$160,000 \$80,000 \$2,000 \$160,001 \$80,001 \$0 The table omits M-FS, since there is no allowable deduction for that filing status - i.e. it is always \$0 Cell AA39 contains a drop-down list of filing statuses: M-FS, M-FJ, S, HOH, QW Cell AA38 contains the amount of taxpayer income Here is the formula: =IF(AA39="M-FS",0,IF(AA39="M-FJ",IF(AA38<='Key tax amounts'!E22,'Key tax amounts'!G22,IF(AND(AA38>'Key tax amounts'!E22,AA38<='Key tax amounts'!E23),'Key tax amounts'!G23,If(AA38>='Key tax amounts'!E24,0,IF(Or(AA39="HOH",AA39="S",AA39="QW"),If(AA38<='Key tax amounts'!F22,'Key tax amounts'!G22,IF(AND(AA38>'Key tax amounts'!F22,AA38<='Key tax amounts'!F23),'Key tax amounts'!G23,If(AA38>='Key tax amounts'!F24,0))))))))) I understand that the problem with the formula is that it fails beyond the first two filing statuses of M-FS and M-FJ; that is, when the IF statement for "M-FJ" fails, instead of jumping to the If statement for either of the other three filing statuses, the formula looks to taxpayer income that is in cell AA38; How can I corrrect this problem The breakdown of the formula is: {Testing for M-FS status} =IF(AA39="M-FS",0, {Testing for M-FJ status} IF(AA39="M-FJ",IF(AA38<='Key tax amounts'!E22,'Key tax amounts'!G22,IF(AND(AA38>'Key tax amounts'!E22,AA38<='Key tax amounts'!E23),'Key tax amounts'!G23,If(AA38>='Key tax amounts'!E24,0, {Testing for any of the other three statuses} IF(Or(AA39="HOH",AA39="S",AA39="QW"),If(AA38<='Key tax amounts'!F22,'Key tax amounts'!G22,IF(AND(AA38>'Key tax amounts'!F22,AA38<='Key tax amounts'!F23),'Key tax amounts'!G23,If(AA38>='Key tax amounts'!F24,0))))))))) -- MZ From: Pete_UK on 22 Apr 2010 06:52 I suggest you amend your table to this: M-FJ S/HOH/QW Deduction amount \$0 \$0 \$4,000 \$130,000 \$65,000 \$2,000 \$160,000 \$80,000 \$0 Then you can change your formula to this: =IF(AA39="M-FS",0,IF(AA39="M-FJ",VLOOKUP(AA38,'Key tax amounts'!E\$22:G \$24,3),IF(OR(AA39="HOH",AA39="S",AA39="QW"),VLOOKUP(AA38,'Key tax amounts'!F\$22:G\$24,2),0))) Hope this helps. Pete On Apr 22, 11:05 am, MZ wrote:> A formula tests for three ranges of income levels for five filing statuses; > depending upon the results, the taxpayer will receive a tax deduction for > \$4,000, \$2,000, or \$0 (i.e., no deduction) > > The table of ranges of income, filing statuses, and deduction ceilings are > as follows: > > Legend: > M-FJ (Married filing jointly) > M-FS (Married filing separately) > S (Single) > HOH (Head of Household) > QW (Qualifying Widower) > > M-FJ            S/HOH/QW        Deduction amount > \$130,000        \$65,000         \$4,000 > \$160,000        \$80,000         \$2,000 > \$160,001        \$80,001         \$0 > > The table omits M-FS, since there is no allowable deduction for that filing > status - i.e. it is always \$0 > > Cell AA39 contains a drop-down list of filing statuses: M-FS, M-FJ, S, HOH, QW > > Cell AA38 contains the amount of taxpayer income > > Here is the formula: > > =IF(AA39="M-FS",0,IF(AA39="M-FJ",IF(AA38<='Key tax amounts'!E22,'Key tax > amounts'!G22,IF(AND(AA38>'Key tax amounts'!E22,AA38<='Key tax > amounts'!E23),'Key tax amounts'!G23,If(AA38>='Key tax > amounts'!E24,0,IF(Or(AA39="HOH",AA39="S",AA39="QW"),If(AA38<='Key tax > amounts'!F22,'Key tax amounts'!G22,IF(AND(AA38>'Key tax > amounts'!F22,AA38<='Key tax amounts'!F23),'Key tax amounts'!G23,If(AA38>='Key > tax amounts'!F24,0))))))))) > > I understand that the problem with the formula is that it fails beyond the > first two filing statuses of M-FS and M-FJ; that is, when the IF statement > for "M-FJ" fails, instead of jumping to the If statement for either of the > other three filing statuses, the formula looks to taxpayer income that is in > cell AA38; How can I corrrect this problem > > The breakdown of the formula is: > > {Testing for M-FS status} > =IF(AA39="M-FS",0, > > {Testing for M-FJ status} > IF(AA39="M-FJ",IF(AA38<='Key tax amounts'!E22,'Key tax > amounts'!G22,IF(AND(AA38>'Key tax amounts'!E22,AA38<='Key tax > amounts'!E23),'Key tax amounts'!G23,If(AA38>='Key tax amounts'!E24,0, > > {Testing for any of the other three statuses} > IF(Or(AA39="HOH",AA39="S",AA39="QW"),If(AA38<='Key tax amounts'!F22,'Key tax > amounts'!G22,IF(AND(AA38>'Key tax amounts'!F22,AA38<='Key tax > amounts'!F23),'Key tax amounts'!G23,If(AA38>='Key tax amounts'!F24,0))))))))) > > -- > MZ