A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Page Layout
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Tax formula tests for multiple filing statuses and income amounts



 
 
Thread Tools Display Modes
  #1  
Old April 22nd, 2010, 10:18 AM posted to microsoft.public.word.pagelayout
mz
external usenet poster
 
Posts: 58
Default Tax formula tests for multiple filing statuses and income amounts

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
Ads
  #2  
Old April 22nd, 2010, 10:45 AM posted to microsoft.public.word.pagelayout
Graham Mayor
external usenet poster
 
Posts: 18,297
Default Tax formula tests for multiple filing statuses and income amounts

This looks like an Excel question? This forum is for Word.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



"MZ" wrote in message
...
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:53 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.