Calculate tax deductions

**A1596833** · 11-17-2009, 08:59 PM

Hello everyone, in my accounting class we are learning how to deduct taxes from the sample employees take-home pay. This is real tedious to do for every employee when you have to switch sheets all the time and figure out how much Federal and Provincial taxes to take off. I was wondering if anyone knows how to use a formula to find the correct value from the table on one of the sheets, and return it to where the deduction boxes are. To help better see this, below is a link to download the file I have. Thank you for your help in advance!

ftp://public@pubftp.myftp.biz/CH21.xls

**Ron Coderre** · 11-17-2009, 09:09 PM

Try these formulas in your posted model:

Sheet: DRILL 21-D2 P.475

Please Login or Register  to view this content.

Copy those formulas down through Row_24

Is that something you can work with?

**A1596833** · 11-17-2009, 10:20 PM

Hi Ron, Thank you so much for your reply. The formula works beautifully. However, in some of the cells it shows a N/A error. Why does it do that to only some of the cells?

**teylyn** · 11-17-2009, 10:53 PM

Hi,

When Vlookup returns N/A, it means that the lookup value is not present in the lookup range. You either want to clean up your data, make sure that E17 can only have valid values, or filter out the N/A with an IF statement like

=IF(ISNA(MATCH(E17,'Provincial Income Tax'!$A$8:$A$67,0)),"",VLOOKUP(E17,'Provincial Income Tax'!$A$8:$N$67,D17+4,1))

hth

**nevintech** · 08-13-2012, 09:23 AM

Originally Posted by A1596833

Hi Ron, Thank you so much for your reply. The formula works beautifully. However, in some of the cells it shows a N/A error. Why does it do that to only some of the cells?

maybe the values in the cells does not meet the requirements for the N/A ones, i have a similar tax workout prob but i was wondering if all the tax deductions can be done in one cell for example

pay of 5500, tax is applied to any income earned above $5000 income tax 25%, $155 for health care, 2% for union fee, 15% for retirement fee, can one formula be derived to calculate all of that?

**arlu1201** · 08-13-2012, 10:01 AM

nevintech,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.