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

Improve this question
0

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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