Help on calculating payroll taxes

[Jokes]

Hi I need help on writing this formula, never posted here but I am hoping someone can help. I have a model with changing payroll data and have made a tab of forecasted cumulative payroll. I want to forecast the different payroll taxes a business will incur in 2013. For example FUTA tax is only applied on the first $7,000 of salary. I want a statement if Cumulative slaray is less than or equal to 7,000 multiply last pay by .6%. However here is where I am stuck. I also need if cumulative salary is greater than 7,000 by less than last pay multiply the amount it took to get to the threshold by .6%. How would I write such a formula?

 

[shg]

      ---A---- ---B----
  1    Salary    FUTA  
  2   $ 1,500   $ 9.00 
  3   $ 2,000  $ 12.00 
  4   $ 1,000   $ 6.00 
  5   $ 1,100   $ 6.60 
  6   $ 1,000   $ 6.00 
  7   $ 1,500   $ 2.40 
  8   $ 1,300   $ 0.00 
  9   $ 1,000   $ 0.00

The formula in B2 and copied down is

=ROUND(0.6% * MIN(7000, SUM(A$1:A2)) - SUM(B$1:B1), 2)

 

[Jokes]

      ---A---- ---B----
  1    Salary    FUTA  
  2   $ 1,500   $ 9.00 
  3   $ 2,000  $ 12.00 
  4   $ 1,000   $ 6.00 
  5   $ 1,100   $ 6.60 
  6   $ 1,000   $ 6.00 
  7   $ 1,500   $ 2.40 
  8   $ 1,300   $ 0.00 
  9   $ 1,000   $ 0.00

The formula in B2 and copied down is

=ROUND(0.6% * MIN(7000, SUM(A$1:A2)) - SUM(B$1:B1), 2)

Wow awesome thank you so much! I am going to try this on all the different taxes in my model and this should work. I may come back with help on the next step which would be to put all my new data into a macro that already exists and just add these line items to the pivot table it is creating. Can't seem to access my work data now, but for now this was a big help!

 

[shg]

You're welcome, good luck.