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
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 a moderator: