Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Pretty Please...
    nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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>.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  11. #11
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula Problem, Tax Related (Excel 2000)

    Hello Nannette,

    I am sure I have your task already up 'n' running.
    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
    Regards,
    Martin

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •