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...
Formulas and Functions
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!

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

    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%

     

    • mathetes's avatar
      mathetes
      Gold Contributor
      Dec 19, 2020

      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

       

       

Resources