Forum Discussion

mford73's avatar
mford73
Copper Contributor
Dec 18, 2020

Need a formula!

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?  

  

Formulas and Functions

4 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Dec 18, 2020

    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%

     

    • mford73's avatar
      mford73
      Copper Contributor
      Dec 18, 2020

      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!

Resources