Sales Taxes

[lsmft]

Started a new worksheet listing all purchased items by name in column
A.
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
at?
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]

lsmft:

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

Example:

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

TypeTable
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

OR

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:
=VLOOKUP(A2,ItemTable,3,0)

Is that something you can work with?

Regards,
Ron

 

[lsmft]

LOL,
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.

 

[tim m]

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:

=IF(A1="Food",0.02,(IF(A1="Cleaning",0.03,IF(A1="Books",0.04))))

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.)