Thanks:  0
Likes:  0

# Thread: Formula for calculating Federal Withholding in payroll

1. I need a formula that will automatically calculate the Federal withholding on an employee's check based on gross wages. Here's my info: Wage Bracket Percentage Method Table for Computing Income Tax Withholding From Gross Wages Employer's Tax Guide Pub. 15A. This Table shows for i.e. if an employee has less than 40 hours and he will make over \$0 but not over \$472.30, then subtract \$241.30 from gross wages, multiply the results by 10%. This is what you would withhold on Federal. But that employee has 40 hrs the next week here is what you're working with: Gross wages are \$472.30 but not over \$1,124.30 subtract 318.30 from gross wages and multiply results by 15%. What formula can I put in that will calculate federal withholdings based on gross wages no matter how many hours the employee has. Here is what I tried but didn't work =IF((gross wages>=472.30),0,(gross wages-318.30)*15%,(gross wages-241.30)*10%)Please Help

[ This Message was edited by: laclewis on 2003-01-14 17:44 ]

2. i think your formula will work if you modify it as follows:

=IF(A1>=472.3,(A1-318.3)*0.15,(A1-241.3)*0.1)

where A1 contains your gross wage number. alternatively you might try this formula instead:

=IF(AND(A1>0,A1<=472.29),(A1-241.3)*0.1,0)+IF(AND(A1>472.3,A1<=1124.29),(A1-318.3)*0.15)

also, i wasn't seeing these numbers that you quoted in your post in the new 2003 pub. 15A so I would just double-check your numbers. and you may want to consider using cell references instead of constants in your formula or formulas as these numbers used in the IRS's withholding calculation tables will change often in the future

hth
kevin

3. Thank you so much for your advice. The first formula worked. You're an angel, I spent 2 days on this. I was looking at the weekly payroll Married with 2 allowances column. I'm self taught when it comes to spreadsheets so I'm still learning. You referred to cell references, wouldn't that mean that I would have to input all of the data from the IRS wage bracket table? Would I just open a new book to do that?
Laura

4. hi Laura, glad i could help - let me show you what i mean by cell references through an example:

in the first formula
=IF(A1>=472.3,(A1-318.3)*0.15,(A1-241.3)*0.1)

instead of having the numbers 472.3, 318.3, .15, 241.3, and .1 in the formula, replace these with references to cells in your spreadsheet that contain these numbers for example, you could place the following data into your spreadsheet:

cell B1 - 472.3
cell B2 - 318.3
cell B3 - .15
cell B4 - 241.3
cell B5 - .1

if these cells already contain data, place the data in other cells. then modify the formula as follows:

=IF(A1>=B1,(A1-B2)*B3,(A1-B4)*B5)

using the above cells in place of the actual numbers in your formula, or whatever cells you placed your data into if not in B1:B5

now when the formula information in the IRS publication changes, which it undoubtedly will, all you have to do is change the data in your cells B1:B5, and the formula will not have to be edited.

hth
kevin

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•