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.
Formula for Federal taxes (Cell G5)
Formula for State taxes (Cell J5)
- the formula is based on a simple
- 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
Data Layout for State Taxes
Note the additional row at the top of the State Taxes . This stops errors on the Employee sheets.