Results 1 to 10 of 10

Thread: Maximum Yearly Contribution Tax

  1. #1

    Maximum Yearly Contribution Tax



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey Everyone!

    I've been trying to solve this issue on my own for a while.. I'm running out of ideas! I've tried so many cheater cells, fractions, etc., that I believe I need to restart from the beginning and rethink this.

    I am a business owner who is making a spreadsheet to compute employee taxes. I have all the formulas figured out for calculating the taxes owed, but have a few taxes that can potentially reach an annual maximum (in Canada we have a maximum pension and employment amount for each year).

    Below, I have a short example of what I'm trying to accomplish with a formula:


    Max Employee Tax: $75.00 (C2)

    Pay Date Employee Employee Tax Looking for:
    January 12, 2018 Bill $25.00 $25.00
    January 12, 2018 Justin $30.00 $30.00
    January 12, 2018 Alex $20.00 $20.00
    January 12, 2018 Phil $25.00 $25.00
    January 26, 2018 Bill $25.00 $25.00
    January 26, 2018 Justin $30.00 $30.00
    January 26, 2018 Alex $20.00 $20.00
    January 26, 2018 Phil $25.00 $25.00
    February 9, 2018 Bill $25.00 $25.00 $25 is the remainder
    February 9, 2018 Justin $30.00 $15.00 $15 is the remainder
    February 9, 2018 Alex $20.00 $20.00
    February 9, 2018 Phil $25.00 $25.00 $25 is the remainder
    February 23, 2018 Bill $25.00 $- <--- After $75.00 is reached, the value should be "0"
    February 23, 2018 Justin $30.00 $- " "
    February 23, 2018 Alex $20.00 $15.00 $15 is the remainder
    February 23, 2018 Phil $25.00 $- " "
    February 23, 2018 Bill $25.00 $- " "
    February 23, 2018 Justin $30.00 $- " "
    February 23, 2018 Alex $20.00 $- " "
    February 23, 2018 Phil $25.00 $- " "

    I'm looking to create a formula that adds the "employee tax" every pay period, but will only reach a certain value (C2).
    the formula must calculate the difference between the "employee tax" and the sum of that employees taxes before the "max is reached.
    additionally, the formula should equal zero for that employee after they have reached the "max tax value (C2)"[/TD]

    Any help would be much appreciated!

    Thanks! - Justin
    Last edited by p45cal; 2018-02-09 at 06:15 PM. Reason: tidy up

  2. #2
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,458
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi and welcome
    could you please post a sample sheet click Go Advanced - Manage attachments) with your data and expected results ?

    I suspect what you are searching for is = MAX(your_formula,C2)
    Thank you Ken for this secure forum.

  3. #3
    Thanks for the Response Pecoflyer,

    I believe I have just attached an example sheet of what I'm trying to accomplish - sorry I'm new to using forums!

    I tried the "Max" formula, but it seems the value equals the "max tax (C2)" every time after the max has already been reached. I'm looking to create a running total for each employee that will return the "taxes paid (F column)" to zero after they have already paid a total of $75 (C2).

    Thanks!
    Attached Files Attached Files

  4. #4
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    360
    Articles
    0
    Excel Version
    Excel 2016
    If you make the formula return a zero when 75 is reached it will always remain zero, just as it remained MAX, this is because it is coded in to the formula to produce that result, the formula has no way of being able to change its calculation range after the 75 is reached. You will most likely need to use a macro, you need to look at your workbook, and its structure, if you really need it and you are ok with a macro we can help construct one.
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  5. #5
    Super Moderator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,006
    Articles
    0
    Excel Version
    Office 365 Subscription
    This can be done easily with a formula, but how does Excel know what monthly contributions the employee should be making (e.g. $25 or $30)?

    Assuming the answer to my question is column D, try this in F7 copied down:

    =MAX(0,IF(SUMIF(C$7:C7,Table2[@Employee],D$7: D7)>=$C$4,$C$4-SUMIF(C$6:C6,Table2[@Employee],D$6: D6),Table2[@[Employee Tax]]))

    Remove the two gaps after colons ( : ) before entering the formula - they are there to stop emojis appearing here.
    Attached Files Attached Files
    Last edited by AliGW; 2018-02-12 at 12:03 PM.
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #6
    Super Moderator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,006
    Articles
    0
    Excel Version
    Office 365 Subscription
    Nobody seems to have looked at the solution offered - had the issue already been resolved? If so, it would be handy to know so that our time isn't wasted. Thanks.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    360
    Articles
    0
    Excel Version
    Excel 2016
    Hi Ali, whilst the formula is great I interpreted the request as >=75 then start again at 0 and calculate the next amounts until 75 is reached again the return to 0
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  8. #8
    Super Moderator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,006
    Articles
    0
    Excel Version
    Office 365 Subscription
    I was really hoping to hear from the OP, Simon, but thanks. My formula produces the results the OP asked for.
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #9
    Thank you so much AliGW!

    Sorry for the later response, tonight has been the first available chance I have had to review your solution. I plugged it into my actual accounting spreadsheet and it worked like a charm!

    I wouldn't have been able to solve this one on my own,

    Thank you again!

  10. #10
    Super Moderator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,006
    Articles
    0
    Excel Version
    Office 365 Subscription
    That's great news - thank you so much for getting back to us. Really glad to have helped!
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

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