Sales Taxes



Started a new worksheet listing all purchased items by name in column
Column B will list how much was spent for each item.
Problem arises when some of the items are taxed at 2 %, some at 3 %,
some at 4%, and some at 6 %.
My question:
Is there a way to denote how much or what rate an item will be taxed
For example:
Row 10, colum B is a grocery food item, hence not state tax, just the
local option taxes.
Row 11, colum B is a cleaning supply item, and will be taxed at the
state S/Tx rate.
I would like to be able to program the whole row for whatever the tax
rate would be. Can this be done?

Ron Coderre


You may want to consider using 2 lookup tables:
ItemTable: for items and their tax type
TypeTable: for assigning rates to each type.


Item TaxType
Apples Food
Armchair Furniture
Carrots Food
Cherries Food
Coat Clothing

Type Stax LocalTax NetRate
Clothing 5% 1% 6%
Food 0% 1% 1%
Furniture 5% 2% 7%

Then....on your input sheet...

A2: (an item)
B2: =VLOOKUP(VLOOKUP(A2,ItemTable,2,0),TypeTable,4,0)
That formula returns the approriate rate for the type of item in A2


You could add a third column to the ItemTable that calculates the
NetRate for the Type associated with each Item (using the same kind of
VLOOKUP formula as in the example above.)

In that case, the B2 formula would be:

Is that something you can work with?



Not sure yet. I'm still scratching my head on this one. I think I may
have gone in over my head here. Thanks much though and I'll see if I
can find how to use that.


How you go about this would depend on how specific or generic you name the
items. Do you just name a food item 'food' or would you put 'bread', 'peanut
butter' etc? If you just give them generic names like 'food', 'cleaning' etc
you could try somehting like this formula:


This will check the contents of A1 and put in a tax rate depending on what
is entered in A1. You could then just copy this formula down the column.
(You would also format this column as percentage.)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sales Taxes 2
Compute Tax? 0
EU orders Apple to pay €13bn in back taxes 11
Tax Invoice 1
If then formulas 4
What's A Billion? 1
Tax rate changes by date 1
Offsetting the vlookup function 4