Share via

FORMULATE IF STATEMENT TO CALCULATE FUTA WAGES

Anonymous
2016-06-23T18:29:11+00:00

Having trouble understanding how to formulate an IF statement for the columns "amount that is taxed" - IF statement needs to take into consideration quarterly wages until the max limit of $7000 is met - tax rate is .80% - The FUTA tax max out is $7000 - keep getting errors when trying to formulate the IF Statement .  Thank you for any assistance that you can lend in helping me to understand how to formulate this statement.

Federal Limit on Wages $             7,000 FUTA Unemployment Rate 0.80%
1st Quarter 2nd Quarter 3rd Quarter 4th Quarter Total
Name
PALMER $             1,800 $             2,000 $             2,000 $             2,200 $             8,000
PASTOR 3,000 3,400 3,400 3,400 13,200
PEREZ 1,600 1,700 1,700 1,700 6,700
PETERSON 1,400 1,400 - - 2,800
$             7,800 $             8,500 $             7,100 $             7,300 $           30,700
1st Quarter Amount that 2nd Quarter Amount that 3rd Quarter Amount that 4th Quarter Amount that Total Taxable
Name is Taxable is Taxable
PALMER $             1,800 $             2,000 $             2,000 $             2,200
PASTOR 3,000 3,400 3,400 3,400
PEREZ 1,600 1,700 1,700 1,700
PETERSON 1,400 1,400 - -
- - - -
Total $             7,800 $             8,500 $             7,100 $             7,300
0.80% 0.80% 0.80% 0.80%
FUTA Tax Payable 0 0 0 0
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-06-24T09:40:45+00:00

    If I have understood you correctly, each person is liable to 0.80% of  tax on the first $7000 of their earnings.

    I will assume the rate is in a cell named "Rate" and the earnings limit is in a cell named "Limit"

    If Palmer's first quarter pay is in B5, her first quarter tax would be

    =Rate*MIN(B5,Limit)

    and this could be copied down for the other people

    For the second quarter

    =Rate*MAX(0,MIN(SUM($B5:C5),Limit)-SUM($B5:B5))

    And this can be copied from the second quarter cells and pasted to the third and fourth quarter cells (so that the cells after the : adjust accordingly)

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2016-06-24T14:58:25+00:00

    Thank you, Mr. Manville, for the quick response.  The formula you are showing will work well with the next spreadsheet I will need to submit for my payroll spreadsheet class.  I admit I only know the basics of Excel and configuring formulas is challenging.  Thanks, again, for your response.  Have a good weekend.

    0 comments
  3. Anonymous
    2016-06-24T14:55:05+00:00

    Thank  you, Mr. Liengme, this is a big help.  I was getting False errors and #Name / #Value errors and didn't know how to proceed.  I'm taking an online payroll spreadsheet class and was only given a few handouts on IF Statements and only knowing the basics of Excel, I find the IF Statements confusing.  Your quick response is most appreciated.  Thank you, again.  Have a good weekend.

    0 comments
  4. Anonymous
    2016-06-24T14:03:19+00:00

    Correction to mine, having seen Bernard's solution:

    Remove the 'Rate*' from both formulas - I was computing the tax, not the taxable amount as requested.

    0 comments