# Thread: Excel Formula Problem, Tax Related (Excel 2000)

1. ## Excel Formula Problem, Tax Related (Excel 2000)

Could someone take a look at this worksheet and read the low-down on my problem in the text box on sheet #2 and give me some guidance on this. It's driving me nuts...this is trying to figure up Federal tax depending on how many dependents they have - their allowance for those dependents, and whether they are married or single...the tax rate has to go into two seperate tables for that, which I've set up and given range names, but I don't think I've set them up right...any help would be appreciated.

2. ## Re: Excel Formula Problem, Tax Related (Excel 2000)

You didn't say where you wanted the 47.7 * # dependents. The formula is:
='Employee Data'!F3*47.7

Regarding the Tax Tables, I would set them up like (name this range "FedS"):
<table border=1><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>219</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0.15</td><td align=right valign=bottom>2121</td><td align=right valign=bottom>285.3</td><td align=right valign=bottom>0.28</td><td align=right valign=bottom>4477</td><td align=right valign=bottom>944.98</td><td align=right valign=bottom>0.31</td><td align=right valign=bottom>10229</td><td align=right valign=bottom>2728.1</td><td align=right valign=bottom>0.36</td><td align=right valign=bottom>22100</td><td align=right valign=bottom>7001.66</td><td align=right valign=bottom>0.396</td></table>

Then I would set one up similarly for "FedM" (with the appropriate amounts)

Then in a cell (eg O4) you will get the "rowindex for the lookup of the tax":
=MATCH(K4,INDEX(INDIRECT("Fed"&'Employee Data'!D3),0,1))

Then you can calculate the tax using:
=INDEX(INDIRECT("Fed"&'Employee Data'!D3),O4,2)+INDEX(INDIRECT("Fed"&'Employee Data'!D3),O4,3)*(K4-INDEX(INDIRECT("Fed"&'Employee Data'!D3),O4,1))
This gets 3 values from the appropriate table. Col 2 (Base amount) + Percentage (Col 3)*(amount over Col 1)

I put the rowindex (O4) it into a separate "intermediate column" since I need it 3 times in the calcs and this is faster: no need to look it up thrice when you can look it up once and then reference it.

Steve

3. ## Re: Excel Formula Problem, Tax Related (Excel 2000)

I like the idea of using the table...been beating myself up over this tax function all day...would it be way to much to ask for you to set up one scenario in the worksheet, and send it back to me for example the FEDS example you showed me...I could then do the next one.
nannette

4. ## Re: Excel Formula Problem, Tax Related (Excel 2000)

You will learn more by just following the directions I outlined in <post#=331498>post 331498</post#>
Create the table as outlined, name it "FedS"

copy my formula to O4
Copy the other formula to C4
O4 and C4 may be copied down the column.

Then Create a similar table (value, base tax, %) for FedM and name it. The formula will read the "S" or "M" and pick the appropriate table.

Steve

5. ## Re: Excel Formula Problem, Tax Related (Excel 2000)

The Engineer is telling the Teacher how to learn <img src=/S/confused.gif border=0 alt=confused width=15 height=20> ... I love it <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>

6. ## Re: Excel Formula Problem, Tax Related (Excel 2000)

I am Chemist NOT an Engineer. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Steve

7. ## Re: Excel Formula Problem, Tax Related (Excel 2000)

Be nice Sammy, even teachers learn new things everyday...who said we knew it all?
Never. I have already done what you said Steve...still working on it...and that was why I asked for the help...i'm getting wierd numbers, and know I got problems going on here, else I wouldn't have asked for a physical example...I'm appreciative though of all your direction.

8. ## Re: Excel Formula Problem, Tax Related (Excel 2000)

This is probably too little too late, but did you look at my bit of rubbish in <post#=16108>post 16108</post#>? It's the 2000 US FIT tables only, not a whole 1040.

9. ## Re: Excel Formula Problem, Tax Related (Excel 2000)

Uhh, now that I looked at the attachment to your original post, you may also want to look at <!post=this thread,209661>this thread<!/post>.

10. ## Re: Excel Formula Problem, Tax Related (Excel 2000)

Bless you...for Federal this is a beauty...I will have fun setting this up. I hope I can get state in much the same manner....will let you all know, but it's never too late, and this has been a real yucky day...your solution worked for federal...lot to finish on it tomorrow, but it went very well
Thank you so much.

11. ## Re: Excel Formula Problem, Tax Related (Excel 2000)

Hello Nannette,

I have downloaded your file and spotted that one of your problems was to decide from which table a LOOKUP would be done. This is done by a similar formula to this:
=IF(A1=something,VLOOKUP(B1,C110,2),VLOOKUP(B1,F1:G10,2))

meaning in column A is something that is decisive for where to search for the value in B. In your case you wanted to search for marital status and return values from the corresponding table.
based on A, B is looked up either in range C and value returned from D, or looked up in range F and value returned from G.

For a "commission" problems (I call this problems where from-this-to-this you get this, from-that-to-that you get that, etc.) I would recommend VBA Function. You can maintain the worksheet much more easily, then.
i apologize if I am off target.

Regards
Martin

12. ## Re: Excel Formula Problem, Tax Related (Excel 2000)

Yeah! Like he said! Or as I'd prefer to put it:
He's NOT an engineer - he's a chemist. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Alan

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•