Tax formula tests for multiple filing statuses and income amounts

[MZ]

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)))))))))

 

[Pete_UK]

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