Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sales charts | Excel Discussion (Misc queries) | |||
Pivot table for reporting sales performance | Excel Discussion (Misc queries) | |||
Calculate Total Sales from a Database | Excel Worksheet Functions | |||
Complex Sales Tax | Excel Worksheet Functions |