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
0
Utf
4/22/2010 9:18:01 AM
word.pagelayout 771 articles. 0 followers. Follow

1 Replies
714 Views

Similar Articles

[PageSpeed] 38

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" <MZ@discussions.microsoft.com> wrote in message 
news:B83C3771-785F-4028-B97C-94FCD3D3411E@microsoft.com...
>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 


0
Graham
4/22/2010 9:45:21 AM
Reply:

Similar Artilces:

Copy Multiple Sheets Into One Sheet
Hi Folks - I track monthly sales in separate worksheets (Jan, Feb, Mar, etc.). At the end of the year, I need to analyze the monthly sales figures on a yearly basis. So, I create a new sheet and copy the individual monthly sheet data to the new sheet. This is a little time consuming. Is there a more efficient way to basically merge and append multiple sheets into one sheet? Thanks in advance. Michael Hi Michael See my site for a few examples http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Michael" <info@homekeyinc.com...

Count formulas
I am trying to do a set of 3 counts between 1-100. Under 80 Between 80-90 and 90+. I have 2 formulas that are working and can't seem to figure the 3rd, could anyone help? 1st =COUNTIF(G4:K22,">89.99") 2nd =COUNTIF (G4:K22,"<79.99") It is the middle one that I am not able to figure out. count all -those two or =sumproduct((g4:k22>=80)*(f4:k22<90)) -- Don Guillett SalesAid Software donaldb@281.com "Barb" <anonymous@discussions.microsoft.com> wrote in message news:045801c3d85f$f193bdb0$a401280a@phx.gbl... > I am trying to do a set of...

PST file size / Scan PST wuestion
I have been saving my company e-mail in personal files for some time. I had a problem wi Outlook 2003 where I could not update my calendar without getting this error message " Could not complete the operation.One or more paramater values are not valid." 1- I now have Outlook 2007 and am having the same issues. I don't remember how I did the SCANPST.EXE thing, buit it worked. Anyone know the process fopr this? 2- Also my PST files are showing up as being over a gig, I have been told the files can go bad if they are too large. IS there a good safe way to split t...

formula to return certain cell
I have column A with names subtotal lines then in the column B $ values I want to create a forumula in column C that searches and return only $'s that are on the lines that contain total in the text string. any suggestions? Seems to me you ought to just learn how to use Data-->Subtotals. However, you should be able to use SUMIF: http://www.officearticles.com/excel/index.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "jerry" <jerry@discussions.microsoft.com> wrote in message news:A8DB40C9-B028-4ACC-825E-FEF59D0778A2@microsoft....

scroll bar incorrectly lumps multiple page numbers together
In Word 2007, there is a 36-page document. The first 10 pages of the document show on the scroll bar as page 2. The 11th page shows page 3, 12th page shows page 4, etc. Therefore, if you wish to print page 11, you have to print page 3 for that output. Any suggestions? Hello Dan DanT wrote: > In Word 2007, there is a 36-page document. The first 10 pages of the document > show on the scroll bar as page 2. The 11th page shows page 3, 12th page shows > page 4, etc. Therefore, if you wish to print page 11, you have to print page > 3 for that output. Any suggestions? ...

ways to separate totals for tax and non-tax items at tender time ?
I am using Microsoft POS 1.0 and a new user. I want to be able to show two separate totals for tax and non-tax items at tender time. Is there a way to do so within POS or do I need to write an add-in ? I am also surprised that I can't even sort per selected column which is often implemented in similar design by clicking on the column's title. At least that way, I can sort tax and non-tax by clicking on the tax column. Thanks in advance for the help, Sea ...

JPG
Just installed MS Office (including OutLook 2000) on a new computer with XP PRO for a client. When they try to open a JPG attachment within OutLook the following error message pops up: 'No file format information can be found in the registry'. In Windows Explore, if I click on tools/folder options/file types, the JPG and JPEG are associated with MS Photo Editor for the open command. If I save the attachment to the hard drive and then double click, the error message above pops up followed by the MS Photo Editor screen and then a 'unknown file format' error. TIA. -- Regards,...

Tax Exempt and tax display
I am new to POS 2009 so this may be a simple question, but it escaped me. 1) We have 2 tax types. How do you setup customers so they are exempt for only one. If you use the exempt feild in customer setup then they are exempt from all. 2) We have each tax seperate on the receipt, but is their a way to display both taxes seperatley on the pos screen? Thanks Doug I am having the same issue but with RMS 2.0..when i set it up to exempt certain part of the tax it gives no tax..plz help "Prairie Computers SK Canada" wrote: > I am new to POS 2009 so this may be a si...

Money 2003 Deluxe
When I go to Reports/Taxes/Tax Software Report I get a summary of what should be included in the various Federal Tax Forms. Everything looks reasonable except for the Capital Gains and Losses Money generated when I bought or sold a stock. It looks like some of the transactions ended up under Schedule C! Can someone give me a hint on where I have to go to tell Money how to handle the generated gains and losses? I have been to all the obvious places like "Catagories" and "Taxes" but I must be missing something. Thanks in advance. In microsoft.public.money, Ken wrote:...

How shall I get access to mails I have placed on the correspondence file
2007.03.05,10:10 EUST Hi I use Outlook 2000. I have placed a lot of mails on the correspondence file. I hope I express myself correct. I started with one Outlook panel named "personal field". In this field I had Inbox, calendar, rough copy, ..... . I did something ( I can't remember what) and suddenly I got two "personal fields" and today I have three "personal fields". Simultaneously all my mails disappeared. And my address book disappeared. If I go to C:\WINDOWS\Local setting\Programs\Microsoft\Outlook I find the files archive.pst (292 MB), rough copy.dbx (...

formula to separate text
Having a cell A1 with this text: Gomez....Fernando...Cost accountant How can I get this content broken in 3 different cells as follow: A2 = Gomez A3 = Fernando A4 = Cost Accountant Your help is much appreciated. Fernando Fernando It depends on what there is to distinguish the end of one section and the beginning of the next. If it's a space, or a comma (or similar), try Data / Text to columns. Post back if it's more complex than that! Andy. "Fernando Gomez" <fgomez@golden.net> wrote in message news:uoJ7LffAEHA.3024@TK2MSFTNGP10.phx.gbl... > Having a cell A1 ...

How do i add the date to subject header of incoming mail?
Hi, How do i automatically add the date yy/mm/dd to the front of the subject header of incoming mail? Would gather its something to do with create rules then run script, but cant workout how to create a script. Thanks Primed The script that can be run by a rule must look like this: Public Sub Whatever(Mail as Outlook) Mail.Subject=Date & " " & Mail.Subject Mail.Save End Sub -- Best regards Michael Bauer - MVP Outlook Category Manager - Manage and share your categories: SAM - The Sending Account Manager: <http://www.vboffice.net/pr...

Turn off formulas
I don't want any formulas to show in a document..I just want the values to show. How can I do this? Thank you. If you're saying that you see in a cell =A1+B1 rather than the result of the formula choose Tools / Options / View and untick formulas. "HT" <asfafd@microsoft.com> wrote in message news:utFVJO%23oEHA.3324@TK2MSFTNGP15.phx.gbl... > I don't want any formulas to show in a document..I just want the values to > show. > > How can I do this? > > Thank you. > > glad to help & thanks for the feedback "HT" <asfafd@...

Query combined with formula
Hi, I have made a query, that gets the parameter from my cell A11. There is always only one result, which is written i cell B11. Now i wish to copy that formula from A11 to the area A12:A3000, so it picks up the parameter from the row, where the formula is placed. The result should be, that a query request data based on A11 and spit it out in B11; another query uses A12 and spits out in B12 (...) Now I need it for now in 3000 rows, and that might expande drasticly over time, so how do I easy copy it down? Regular copy/paste only copy the formula or value of the cells, not the queries... ...

Tax Invoice automated Tax number
I used Office 97 for a long while. In this Office Software Excell had the best Tax Invoice program or sheet. This sheet had a automated numbering system. Who knows of this and can tell me why in the Office 2003 Software this is not available any more ... this is very disturbing How can I use this Tax numbering system in the new Office 2003 - the numbers should automate themselves ...

how can I open a pdf file in Publisher 2003
I need to open and edit a PDF file in Publisher but Publisher can't open PDF files has anyone got any ideas? Philip Smith <Philip Smith@discussions.microsoft.com> was very recently heard to utter: > I need to open and edit a PDF file in Publisher but Publisher can't > open PDF files has anyone got any ideas? Why do you need to open a PDF file in Publisher? Until we know what you are intending to do and why you need to open a PDF in Publisher, it is difficult to recommend an appropriate course of action. -- Ed Bennett - MVP Microsoft Publisher Do you have Adobe Ac...

Clearing contents without deleting formulas
I need to know how to clear the contents of multiple cells without deleting the formulas. I know I can do this, just can't figure it out. Thansk! Hi Wendy F5 Constants Ok Press the delete key -- Regards Ron de Bruin http://www.rondebruin.nl "Wendy" <anonymous@discussions.microsoft.com> wrote in message news:F942D2AC-8A1D-4852-8EB2-75AF033AA24B@microsoft.com... > I need to know how to clear the contents of multiple cells without deleting the formulas. I know I can do this, just can't figure it out. Thansk! You can't really do that. Your formulas should b...

Price Includes Tax
We have a very simple store that sells 4 sizes of ice cream. Size A is 2.75 Size B is 3.50 Size C is 4.25 Size D is 5.00 These prices include tax which is 7.8%. So what I did was entered item pricing of the following to account for the tax. Size A is 2.55 Size B is 3.25 Size C is 3.94 Size D is 4.64 This works great except for when the customer orders multiple quantities. Because I have rounded up the pricing it eventually rounds up making the total sale value a penny or two higher. For example: If the customer gets 3 Size D's the final price should be $15.00. However, the POS...

Offset formula help!!
I am trying to define ranges each of my ranges is characterised by an identifier in this case "D1" then directly below it are property descriptions. So my sheet looks like this: D1 XXX XXX XXX D2 XXX XXX XXX My problem is that when i use this formula it counts all text values so when i want to select range D1 rather than selecting D1 and the three rows below it, it selects D1 and 8 rows below D1. Any ideas on how I can make the formula select the range properly? =OFFSET(Sheet1!$A$1,MATCH("D1",Sheet1!$A:$A,0)-1,0,*COUNTA(Sheet1!$A:$A*),5) -- Vlad999 -----------------...

Formula for a count of names?
Is their a formula for a count of names?? example: Joe Smoe Larry Lie Susan Kett and have a formula add up how many names their are?? Have a look in HELP index for COUNTA -- Don Guillett SalesAid Software donaldb@281.com "Alesha" <Alesha@discussions.microsoft.com> wrote in message news:4FB17D90-AFCD-415C-B852-1A994FDCDCAD@microsoft.com... > Is their a formula for a count of names?? example: > > Joe Smoe > Larry Lie > Susan Kett > > and have a formula add up how many names their are?? Alesha See Chip Pearson's site for formula(s) to count unique...

HELP! PST file has 150MB file size but no msg could be read from it.
I have a PST file here which the size is 150MB, I know there are a lot of messages inside because I was just looking at them yesterday. For some reason, when I opened it today by Outlook, no data from this PST could be found and that realy freaks me out because those messages are very importnat. >From Outlook Folder List, when I right click on the [Archive Folders], go into Properties and click on [Folder Size], it shows 0k for everything and the total size is 0k. By searching on the internet I found a tool called scanpst.exe and I have ran it, it seems like it was completed and here is ...

Removing formulas
I have 2 worksheets, on worksheet 2 I created figures by using formulas using refrences to the other worksheet (worksheet1). I now need to use the new figures on worksheet 2 to create a new worksheet using different formulas to create yet a different set of figures. Every time I try to use the new formula on worksheet 2 it just puts a 0 in the cell and says something about Circular formula. I have tried to copy and paste the worksheet into a new excel window but it just copies the formula with it. I want to be able to get rid of all the formula on wsheet2 but still leave the figures...

sp 6 Payroll tax update error
client attempted to install Rd 6 payroll tax update and code. The TX.cab file was successful but code update was successful on 2 databases then errored out on the third. She rec'd error: 'The stored procedure smGrantAccessOnAccountMSTR returned the following results: DBMS: 0 MicrosoftDyanmics GP' Has anyone experienced the same? Thanks, Debi And the error is? DBMS: 0, Microsoft Dynamics GP: ? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot....

adding file extension
When creating a MFC application in Visual Studio .Net, the wizard gives the option of giving a File Extension that you would want your application to hold. However entering the file extension is optional. If it is skipped here, then is it possible to assign an extension to the application later? You can do it later as well but it will easier to see the code that wizard generates and simply add that in your code. Its not too much code so finding the difference should be trivial. ---- Ajay Kalra ajaykalra@yahoo.com ...

RMS Taxes
Hello. We have a unique setup whereas we are both a contractor and a retailer. Some jobs require us to pay the taxes to the city where the installation is, while people who walk into the store need to pay retail taxes. We currently calculate sales tax based off the customers zip code, which works well. If, for some reason, the zip code is entered in incorrectly or is not known it will automatically collect the retail tax (which is higher than the others...so at least we are overcollecting). Here's the problem. If customer XYZ comes into the store and purchased $500 work of prod...