Tax formula tests for multiple filing statuses and income amounts
in [General]
|
Prev: How do i recover a file ive deleted by mistake?
Next: what row do I start an address for a window envelope?
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 <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 |