Need a formula!

Copper Contributor

I am trying to budget the state unemployment tax owed on an employee's wages by month, with the calculation stopping at a max amount of 7,700 in wages. So, the first 7,700 in wages earned in the year will be taxed at 2%, but once the wages earned reach $7,700, the calculation stops. 

 

For example, if an employee earns $3,200 in January, the tax amount for Jan. will be $64.  Then, the employee earns $3,200 in February, and the tax amount will be $64.  When the employee earns $3,200 in March, only $1,300 of the $3,200 is taxable.  ($3,200 + $3,200 + $1,300)=$7,700 (max taxable wages)  How can I set up a formula that will stop calculating once the wages meet the max?  

  

4 Replies

@mford73 without the sheet to see how things are set up it is a little hard (i.e. are the months on different sheets or same sheet but non-contiguous cells) to give a specific formula so here is an option in pseudo-code:

=MAX(0,MIN([This Month],[This Month]-[Cumulative]+7700) ) * 2%

 

@mtarler 

 

I'm attaching a sample file.  Can you show me how to input the formula in the February and March State Unemployment Tax Owed fields?  I gave it a shot, but came up with $64 in February and $64 in March, when March should be $26.  

 

I'm thinking my problem is with the cumulative part.  Maybe?

 

Thanks so much!

@mford73  yep, that was the problem.  Here is the formula to use in Jan tax and then copy to the right:

=MAX(0,MIN(A2,A2-SUM($A2:A2)+7700))*2%

 

@mtarler    @mford73 

 

Here's a variation using a named ranges for the maximum taxable amount (Max2Tax) and the Tax Rate.

 

This is an ideal situation for naming the variables, rather than hard-coding, because it's entirely possible that next year or the year after, one of those numbers will change. Using named ranges allows the formulas to remain constant, with only the need to change the value assigned to the name.

=MAX(0,MIN(A2,A2-SUM($A2:A2)+Max2Tax))*TaxRate