Add range of cells until value is reached, then return zero in future cells

[Kieradee]

Hello,
First, my apologies if this is answered already on the forum. I read through some of the posts as a guest but I'm not sure what formula I really need.

I'm creating an excel worksheet for a friend to use to gross up her draws as she takes them, then calculates the taxes that should be withheld from that gross up. She only needs to input the draw she's taken into Column B (Column A is for the date of the draw) and the gross wages appear in Col D, FICA Withheld appears in Col E, Medicare Withheld appears in Col F, FWH appears in Col G and SWH appears in Col H. That part I've completed.

Since she has a corporation and will pay payroll as an employer, I want to calculate the amount of her employer taxes for each draw as well. The purpose of this worksheet is so she can see how much of a total tax liability she's incurring as she takes these draws.

On the employer tax side:

FICA employer match is 7.65% up to gross wages of $118,500 or max amount of tax = $9,065.25
SUI is 2.60% up to gross wages of $36,900 or max amount of tax = $959.40
FUTA is .80% up to $7,000 or max amount of tax = $56.00

Col L is FICA match, Col N is SUI, and Col O is FUTA. Again, gross wages are in Col D and it's a calculation since it's the gross-up of her draw.

I need Col L to calculate the FICA on her gross wages in Col D until the maximum of $9,065.25 is reached, then return -0- in that column after the max is reached.

I need Col N to calculate SUI on Col D gross wages until the maximum of $959.40 is reached, then return -0- in that column after the max is reached.

I need Col O to calculate FUTA on Col D gross wages until the maximum tax of $56.00 is reached, then return zero in that column after the max is reached.

Col Q returns the total of all Employer taxes for each draw.
Col S returns the total for both the withheld taxes and employer taxes for each draw.
And Col U will return the total for total taxes accruing as each draw is entered.

I've uploaded the worksheet and hope I've explained it well enough.

Thank you in advance for your help!

 

[Faseeh]

Hi Kieradee

you have explained sheet very well but can point to the cell which need to worked out i think the second range of cell with employer taxes needed to be work out. What is the correct answer you are expecting for data of row 7 and 8?

 

[Kieradee]

Hello, Faseeh,

Thank you for your response. This is what I need help with:

I need Col L to calculate the FICA on her gross wages in Col D until the maximum of $9,065.25 is reached, then return -0- in that column after the max is reached. It can either be a calculation of her gross wages in Col D * 7.65%, then Col L stops calculating when she's reached $9,065.25 and only returns zero in that column, or it can just use the calculation in Col E since its matching that.

I need Col N to calculate SUI on Col D gross wages until the maximum of $959.40 is reached, then return -0- in that column after the max is reached.

I need Col O to calculate FUTA on Col D gross wages until the maximum tax of $56.00 is reached, then return zero in that column after the max is reached.

Thank you.

 

[candybg]

There are two ways to do this - one would be a single formula that uses and IF statement to check to see if gross wages are over the Fed max or not. The other option would be to create an extra column, which could be hidden, to show you gross wages YTD, essentially totalling the wages in Col D.

Do you have a preference?

Either way, I would put ALL the constants in separate cells, as you did with the tax rates. IE - one constant would be the Fed limit for wages subject to SS tax being in a single labeled cell, rather than also have text in the cell.

 

[Kieradee]

Hi, Candy,

I like your idea to have a column to total YTD wages. And it doesn't have to be hidden. It would be good for her to see what her gross wages are totaling as she goes. Don't know why that didn't occur to me. I still want to limit the calculations in L, N and O when they reach their respective max, which I don't know how to do.

Having the constants in the cell above the tax rates for Col's L, N & O also makes sense.

 

[candybg]

OK - I've made a few changes. Added the YTD gross wages in Col C (your unused column). I added a YTD FICA wage column, so you can see the actual formula for it. Formula in L7 and L8 (slightly different for the first one) added to calc FICA tax due (FYI - this is actually Soc Sec tax due, not FICA - as FICA is both SS tax and MC tax).

From this, you might be able to write the formula yourself for SUI & FUTA, with or without a helper column (ie my new Col M).

Of course I'd be glad to write those formulas for you if you like.

Another suggestion - set this up as a TABLE and you wouldn't need to keep adding rows for formulas, they would update as you type in a new row : )

Hope this helps!

 

[Kieradee]

This is great, Candy! Thank you!!

I've never worked with tables. I'm taking a class next month but it's a beginners course so I can learn about Macro's. I'll read up on tables but it's probably beyond my expertise. Thank you for pointing out the error in the FICA label. I'll change that. I'll see if I can write the formula for N & O. I won't be back until later this afternoon. Thank you so much!!