VLOOKUP Question

[BobA]

Don't you have to use ROUNDDOWN to the next smaller value?

The IRS table he's working with do not require rounding search

values if the table is properly configured to IRS guidelines. Not sure

why Bob is 'hung up' on the rounding!!! --

Garry

[Since I can't get any of the formulas provided to do exactly what I want, this is what I've decided.]

If the taxable income came to $189.37, this would round down to $189.00.

You have two choices on the table for a value of $189.

1. $186 - $189 - $25.00
2. $189 - $192 - $26.00

I've been using the first choice, or the lower tax value - $25.00.

If the taxable income came to $189.51, this would round up to $190. $190 falls between $189 and $192 for a tax value of $26.00.

This is how I've been doing things for years.

I guess, now that I think about it, I was using the table wrong from the beginning.

It seems the simplest solution is to just use line 2 above for $189 AND $190, or $26.00, which is what my original formula actually yields.

Thanks to all

 

[GS]

Don't you have to use ROUNDDOWN to the next smaller value?

The IRS table he's working with do not require rounding search

values if the table is properly configured to IRS guidelines. Not
sure

why Bob is 'hung up' on the rounding!!! --

Garry

[Since I can't get any of the formulas provided to do exactly what I
want, this is what I've decided.]

If the taxable income came to $189.37, this would round down to
$189.00.

You have two choices on the table for a value of $189.

1. $186 - $189 - $25.00
2. $189 - $192 - $26.00

That's NOT how the table works!!! According to the doc you linked to,
it works like this...

at least $186 and less than $189 >$25
at least $189 and less than $192 >$26

I've been using the first choice, or the lower tax value - $25.00.

If the taxable income came to $189.51, this would round up to $190.
$190 falls between $189 and $192 for a tax value of $26.00.

This is how I've been doing things for years.

I guess, now that I think about it, I was using the table wrong from
the beginning.

No kidding!

It seems the simplest solution is to just use line 2 above for $189
AND $190, or $26.00, which is what my original formula actually
yields.

The correct solution is to structure your table to work as expected by
IRS. This requires following my suggestion of making the max value
$0.01 less...

$186 > $188.99 > $25
$189 > $191.99 > $26

...and just set number format to no decimal so it displays as...

$186 > $189 > $25
$189 > $192 > $26

...and use a header that indicates >= for the min value and < for
the max value. Like I said.., I've already got this working correctly
for all tables and so is how I know the what&how of it! Here's my
formulas...

TaxS (single)
=VLOOKUP(WagesPaid,TaxTableS,WA_Ndx+TableOffset,1)

TaxM (married)
=VLOOKUP(WagesPaid,TaxTableM,WA_Ndx+TableOffset,1)

...to get both taxes from each table, repectively.

WagesPaid is the name of the col where I enter payroll amounts.

WA_Ndx is the name of the col where I enter the Withholding Allowance
index for each payee.

(Both these cols are row-relative, column-absolute)

I use a local scope defined name for TableOffset that stores the value
3 since there's 2 cols with the Min/Max range and the 1st Withholding
Allowance col is zero. The tax amounts start in col3 of the tables so
adding the index to that refs the correct col.

Here's the formula I use in the Tax Amount col...

=IF(MaritalStatus="S",TaxS,TaxM)

...where MaritalStatus (row-relative, column-absolute) is the name of
the col where I enter S or M for each payee.

TaxS/TaxM (row-relative, column-absolute) are the names of the cols
that contain the aforementioned formulas to pull values from each
table. These are grouped so I can collapse (hide) their cols. The sheet
structure for cols is...

Date | Name | Wages Paid | WA Index | Tax Amount | Marital Status

...followed by TaxS | TaxM | | TaxTableS | TaxTableM.

The worksheet templates are...

Daily,Weekly,BiWeekly,Monthly,SemiMonthly

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[BobA]

2. You do NOT have a choice if the taxable income is $189. Look at the column headings closely: $189 is clearly not LESS THAN $189; it is AT LEAST $189 which means, if you are using the wage bracket method, the amount towithhold (with one allowance) is $26 and NOT $25

Thank you Ron, that makes sense, and now that I think about it of course you are right. (I just wanted to see if there was a simple formula that worked for the way I HAVE been doing it.)

Thanks to you to Gary. I'll play around with your reply.

 

[GS]

This is part of a table N2137

60 $186.00 $189.00 $25.00
61 $189.00 $192.00 $26.00
62 $192.00 $195.00 $26.00

I use this formula in F12:

=IFERROR(VLOOKUP(ROUND(C14,0),N2137,3,1),"")

This is the value in C14--$189.27

This formula returns a value of $26.00, but I want it to return
$25.00.

What am I doing wrong?

Bob,
The Percentage withholding method is much better suited to
computerized determination of the amount to withhold. The Lookup
tables are better suited for manual work.[/QUOTE]

Tell that to the accounting software vendors who make $$ off providing
tricky tax tables if you subscribe to their app's payroll feature. I'd
agree that the % method is certainly easier to maintain yearly updates
to tax rates, but tables are a copy/paste deal at worst IMO!

I get new tax tables automatically when they become available, and so
is why I went with using them. Though it seems that using % method
would be much easier than scouring over 10K cells over 10 tables!

Perhaps I'll revise my project to include % method and post it for
download so users can go with either method!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

There are 8 tables (actually 16, considering that married and single
are separate sub-tables) used for the percentage method, with seven
brackets in each one.

However, you can simplify it even further -- you really only need to
use the Annual table, and just adjust the numbers for the actual pay
period. It's off a bit for the daily/miscellaneous table, very
close for the others, and probably close enough for gummint work
(acceptable alternative method in IRS-speak). It's the method I
used for years when I was involved in such things.

I should add that I haven't tested the differences between using the
annualized method with the annual table for daily/miscellaneous wage
periods, because it never became necessary. However, the IRS does
publish acceptable tolerances to define what is "close enough" in
Publication 15A. And, more importantly, in that Publication, they
also write that the Annualized method is an acceptable alternative.
They define it:

Annualized wages. Multiply the employee's amount of wages for the
current payroll period by the number of payroll periods in a year to
determine the annualized wages. Using your employee's annualized
wages, figure the withholding using Table 7—ANNUAL Payroll Period in
the Percentage Method Tables for Income Tax Withholding in
Publication 15 (Circular E). Divide the amount from the table by the
number of payroll periods in the year, and the result will be the
amount of withholding for each payroll period.[/QUOTE]

Yeah, I recall reading that in the latest release. I'm actually taking
a closer look at those % method tables to see how I might add them to
the period sheets in my project.

We don't have a daily/misc table here in Canada, but we do have the
alternative 'calc' in addition to the lookup tables. As with IRS, our
CRA deems part-time/casual work the same way. What IRS terms 'WA' is
termed 'CC' here, and is identical in levels structure (0 to 10). The
IRS tables are better prepared than what we get from CRA (which look
like they were done by a school kid), IMO!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Ron,
I've added the Percent Method sheet to my project. If you'd like to
review the project before I upload it for public access, I'm willing to
email it to you. I don't like posting email personal addresses and so
suggest we proxy via someone we mutually have email contact with. I
suggest Rob Bovey, and I'll give him a heads up right now...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Gary,
I don't believe I know Mr. Bovey, other than by reputation. And I
don't have his email in my address book. If you have PGP, you could
encrypt the file using my public key, and then post it at some
accessible site such as drop box, onedrive, etc; and then post a link
here. I'm not sure I have time to do a proper critique, but I'd be
glad to take a look at it if you like.

Rob knows you! You can email him at www.appspro.com and he'll forward
to me. If you don't want to do that then try here...

http://www.solutionsxpress.com/sitemail/sitemail.htm

...and I'll be notified when it arrives.

Otherwise, I don't know what PGP is. I use box.com for public access
but I'd like your eval of this before I upload it. I'll share more
details once we establish email contact.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

PGP is a public key encryption tool. See
http://en.wikipedia.org/wiki/Pretty_Good_Privacy for details

I already looked this up. Thing is I've known about public/private key
cryptography for about 10 years from developing my own app licensing

Aw heck, just reverse and make the obvious substitutions to get to
me:



mocTODenilnodlefnesorTAdlefnesornor

That works too, but the link I gave you is a private mail system which
I'll be using from now on for this purpose.

BTW, I found refs to your email online exactly what you posted. Just
was waiting for your consent to proceed...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion