0

I have been attempting to make a formula to easily calculate federal income tax rates. The picture attached is the withholding table that I've transferred onto a sheet. These are the thresholds I am attempting to build this formula around. The second picture attached shows a payroll sheet where the formula would then go. The tax is based off the gross income. So if the gross income is between one of the ranges, it should automatically place the corresponding tax amount. I've tried multiple IF formulas and have had no luck, it's always an error. If someone could give a possible formula for it, or a recommendation for formula type that would be great.

enter image description here

enter image description here

3
  • 1
    Hi, welcome to WebApps. The good news is that this is very easily done (though I couldn't quickly locate a precedent). Would you please edit your question to show the values should be displayed for a successful outcome. For those of us not familiar with US?? taxes, please explain what the function of the "Allowances" columns, and how they impact on the result.
    – Tedinoz
    Jun 17 at 2:29
  • If you are still looking for help with this, A.) please respond to Tedinoz above and B.) please share a link to a sample spreadsheet that is set up exactly like your real spreadsheet, so that the volunteer contributors here already have your data to work with (it is highly unlikely that any will hand-type your data into their own sheet). In addition to what Tedinoz asked above, we would need to see where the number of allowances is listed for each person (which is not shown in your post's bottom example image at all).
    – Erik Tyler
    Jun 20 at 19:55
  • @Tedinoz link Here is the link to a sample document. Both Federal and State withholdings are determined by the tables in the document. Each employee is also filing with a certain number of "allowances" shown on their sheets. This determines the column used for their withholdings. They are based off of gross pay. So say someone has a gross pay of $200 and files 2 allowances, their withholdings would be whatever is in the corresponding column and row of gross
    – John
    Jun 22 at 1:44

1 Answer 1

0

Formula for Federal taxes (Cell G5)

=ArrayFormula(if(B5:B="","",vlookup(F5:F,FEDL_WH!$A$4:$D$54,$B$2+2,1)))

Formula for State taxes (Cell J5)

=ArrayFormula(if(B5:B="","",vlookup(F5:F,STATE_WH!$A$4:$D$55,$B$2+2,1)))

Comments

  • the formula is based on a simple VLOOKUP
  • the index is calculated by taking the number of allowances and adding "2".
  • the data is treated as "sorted" which means that it will find the closest match.

Data Layout for Federal Taxes

Federal


Data Layout for State Taxes

Note the additional row at the top of the State Taxes . This stops errors on the Employee sheets.

State

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.