Maximum Yearly Contribution Tax

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

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)

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!

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.

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.

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.

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

I was really hoping to hear from the OP, Simon, but thanks. My formula produces the results the OP asked for.

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!

That's great news - thank you so much for getting back to us. Really glad to have helped!