Function to deduct amount and percentage

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

You can do something like this:

1) create a table that mirrors the one you posted

2) create a table that use the table in (1)

the table on the right is tited "Withholding Lookup" and contains the same information in the table you posted

The table on left uses the information in the table "Withholding Lookup".

For the table on the left (titled "Table 5" in the image I posted):

column A is where you enter the amount and column B is the amount computed from information in the table on the right.

B2=VLOOKUP(A2,Withholding Lookup::A:C, 3)+VLOOKUP(A2,Withholding Lookup::A:C, 2)×(A2−VLOOKUP(A2,Withholding Lookup::A:C, 1))÷100

this is shorthand for select cell B2 and type (or copy and paste from here) the formula:

=VLOOKUP(A2,Withholding Lookup::A:C, 3)+VLOOKUP(A2,Withholding Lookup::A:C, 2)×(A2−VLOOKUP(A2,Withholding Lookup::A:C, 1))÷100

you can fill down if you need to by select cell B2 then hover the cursor over the bottom edge then drag down the yellow circle as needed

I like your tables 😉

what is not working? Are you copying an pasting from here?

you should select (as shown), then copy, then paste into cell B2 ( if you are following my example).

make your lookup table look like the one I posted. you omitted the first row which is all zeros.

There is a base amount:

What do you believe the Withholding (reported in cell B1) should be?

Wayne Contello wrote:

I like your tables 😉

Thanks!

J

?

1600 is greater than 1582 and less than 3025. so the base is:

195.4 plus 25% of the excess.

the excess is (1600-1582) which is 18

0.25*18 = 4.5

so 195.4 + 4.5 = 199.9

I had a typo in my formula which results in being off by a percent.

B2=VLOOKUP(A2,Withholding Lookup::A:C, 3)+VLOOKUP(A2,Withholding Lookup::A:C, 2)×(A2−(VLOOKUP(A2,Withholding Lookup::A:C, 1)−1))÷100

I had to subtract one from the base amount.

copy this formula and paste into cell B1, then fill down as needed.

According to the original table I posted, for $1,600 withholdings should be $595.4.

Please show how this is so.