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

    Payroll woes (Excel 2000)

    If anyone would take the time on this, I would be ever so appreciative...

    I am supposed to subtract from the adj gross on this payroll sheet 36.67 for every dependent, one lady has 7, (she only earns 500.00 or so a month)...and also subtract from adj gross 47.75 for every dependent
    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: Payroll woes (Excel 2000)

    You have 2 "adjusted values" to lookup (not just 1)
    For the state you use the (gross salary - dep *36.67) to do the lookup
    For the Fed you use the (gross salary - dep *47.7) to do the lookup

    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: Payroll woes (Excel 2000)

    The formulas make sense, but are not working. I tried both yours and the above listed formulas in other feedback from sdkckr...could you attach my sheet back to me with one of your formulas in place? Either or, just so I can see it in form...because it doesn't work for me.. It would help a lot.
    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>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Payroll woes (Excel 2000)

    Sorry, my formula had a typo in it. I should know better that to post a formula that I haven't tested.

    Your sheet is attached with the state adjustment formula.
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Payroll woes (Excel 2000)

    I don't think you use both adjustments at the same time. You use the state adjustment when calculating the state tax, and you use the Federal adjustment when calculating the Federal tax. I think that want a formula like below for the state:

    <pre>=if((Y2-(M2*36.67))<0,0,Y2-(M2*36.67))
    </pre>


    and one like below for Federal:

    <pre>=if((Y2-(M2*47.70))<0,0,Y2-(M2*47.70))
    </pre>



    Edited by LegareColeman to correct typo in formula.
    Legare Coleman

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

    Re: Payroll woes (Excel 2000)

    Once I made a column as well for the Fed like you did the state, it all seems to be going to good to be true. Thank you so much again. You guys are true WIZARDS!!
    Nannette <img src=/S/salute.gif border=0 alt=salute width=15 height=20>
    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>

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

    Re: Payroll woes (Excel 2000)

    Legare, or Anyone:

    Am I to understand that my Employee Number #3 (Francis) is not an error. Can I have a negative taxable value? The worksheet seems to be going fine except for that...Am sending it back just so you can peek at her pay line...she's driving me nutty!!!

    Thanks
    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
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Payroll woes (Excel 2000)

    At a quick glance, I think that there are a lot of problems with what you are doing. For example, you calculate FICA with this formula:

    <pre>=IF(Y4<=68400,Y4*0.062,0+68400*0.062)
    </pre>


    If I understand the sheet, Y4 contains a monthly pay amount. However, you are comparing it against an annual maximum amount of 68,400. What you want to compare to 68,400 is the YTD pay.

    AF4 contains the sum of V4, and AB4:AE4. AB4:AE4 looks like it contains a mix of Federal and State deductions. I don't believe that those should ever be mixed together. AB4 is FICA, and I don't think that should be subtracted from Gross income before calculating the tax due. I am not a tax expert, so I don't know for sure. I also don't see where you are calculating the Medicare tax.

    AG4 subtracts AF4 from AA4. AA4 has already had V4 subtracted from it, and AG4 includes V4, so you are subtracting V4 twice.

    I found the above with only about 2 minutes of looking, and did not examine any of the cells with the lookup formulas. I think you need to have someone who understands taxes (Federal and your state) go through this with you.
    Legare Coleman

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

    Re: Payroll woes (Excel 2000)

    Thank you Legare,
    I am no payroll expert either...not even an amateur, and that is why I am having major difficulties with this worksheet. If anyone out there knows payroll, please take a look at this sheet, and tell me some of my errors...I will go through and take a look at all the errors you pointed out Legare and get them fixed. Thank you so much.
    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>

  10. #10
    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: Payroll woes (Excel 2000)

    I suggest getting some "representative" test data that covers a range of values and criteria and test the calcs vs the values calculated by manually.

    There are issues with the "logic" of the calc (you need a tax expert to help with this- "are you doing the correct calculation") vs is the formula correct (which is a setup issue).

    If you are worried about a lookup, try different values in the cell to lookuop (forgetting all the other previous calcs) to make sure the lookups give the numbers you expect. You must check these things "in parts" to make sure each "part" calcs as expected. You need to check something within each range and even the extreme values to ensure you have the calcs correct.

    Steve

Posting Permissions

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