From: MZ on
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
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 <M...(a)discussions.microsoft.com> 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