Sales Taxes
 

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?


--
lsmft
------------------------------------------------------------------------
lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=548118

Sales Taxes
 

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=548118

Sales Taxes
 

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.


--
lsmft
------------------------------------------------------------------------
lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=548118

Sales Taxes
 

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="B ooks",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.)

"lsmft" wrote:


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.


--
lsmft
------------------------------------------------------------------------
lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678
View this thread: http://www.excelforum.com/showthread...hreadid=548118