Apple Event: May 7th at 7 am PT

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Function to deduct amount and percentage

Hey Guys,

I’m creating a payroll sheet and need some help programing a function. I need to figure out ‘Net Wages Earned’ after-tax deductions, I’ve programed functions for most of the info, but have not been able to figure out how to automatically deduct the appropriate amount based on the IRS’ complex tables. Ive attached one of the tables for an example. Any ideas?

Any help would be greatly appreciated.

(The above table to the IRS, I found it on their website. No copyright infringement is intended)

iOS 7.1

Posted on Apr 8, 2014 11:12 AM

Best reply

Jerrold Green1

Level 7

30,211 points

Posted on Apr 8, 2014 11:52 AM

Byron,

Here's a solution that expands the calculations for clarity. It could be compacted to a one column calculation if desired.

The expressions in the calculations table are as follows:

Column A is the earnings input column.

Column B: =VLOOKUP(A, Lookup Tax Table::A:C, 1, 1)

Column C: =VLOOKUP(A, Lookup Tax Table::A:C, 2, 1)

Column D: =VLOOKUP(A, Lookup Tax Table::A:C, 3, 1)

Column E: =C+(A2−B2)×D

I hope this gets you on your way.

Jerry

View in context

22 replies

Apr 16, 2014 8:42 AM in response to Wayne Contello

This is what I have after filling in you formula. I'm trying to find why the Withholding amount is less than 199.9.

Apr 16, 2014 8:45 AM in response to The Other Byron

Your look up table is not correct. Use this:

Amount	Percent	Base
0	0	0
164	10	0
513	15	34.9
1583	25	195.4
3026	28	556.15
4526	33	976.15
7954	35	2107.39
8964	39.6	2460.89

Apr 16, 2014 8:59 AM in response to The Other Byron

Byron,

You need to consult the other table, withholding allowances, to find what the allowance is, and multiply that by the number of allowances and subtract that from period earnings, all before you come to this table. You haven't told us anything about the previous calculations.

Jerry

Apr 17, 2014 6:40 AM in response to Wayne Contello

Your look up table is not correct. Use this:

Amount

Percent

Base

0

0

0

164

10

0

513

15

34.9

1583

25

195.4

3026

28

556.15

4526

33

976.15

7954

35

2107.39

8964

39.6

2460.89

Gotcha. Changed my table and it works great now. Thank you again.

Apr 17, 2014 6:47 AM in response to Jerrold Green1

Jerrold Green1 wrote:

Byron,

You need to consult the other table, withholding allowances, to find what the allowance is, and multiply that by the number of allowances and subtract that from period earnings, all before you come to this table. You haven't told us anything about the previous calculations.

Jerry

Right, this is what I have.

Certified Payroll:

Apr 17, 2014 6:49 AM in response to The Other Byron

Byron,

So, is everything ok then or are you still needing some help?

Jerry

Apr 17, 2014 8:12 AM in response to Jerrold Green1

Jerrold Green1 wrote:

Byron,

So, is everything ok then or are you still needing some help?

Jerry

So far so good. Thanks again for seeing this through with me.

Function to deduct amount and percentage