Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula based on prefix
Hi
I need a different markup formula based on prefix in inventory list ie: ppbolt would get 15% markup (prefix is pp) (Description) detnut would get 20% markup (prefix is det) (Item Code) 3rnail would get 25% markup (prefix is 3) (Item Code) col A would be item code col B would be description (the pp prefix is on some of the items here) col C would be cost col D would be retail Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula based on prefix
Create a table on the side, two columns, like so:
A B pp 0.15 det 0.20 3 0.25 Now you can write a formula to analyze the first few letters of each inventory. Let's say inventory list starts at D1, and the cost for each inventory is at E1: =E1+E1*(IF(LEFT($D1,3)="det",VLOOKUP($D1,$A$1:$B$3 ,2,FALSE),IF(LEFT($D1,2)="pp",VLOOKUP($D1,$A$1:$B$ 3,2,FALSE),IF(LEFT($D1,1)="3",VLOOKUP($D1,$A$1:$B$ 3,2,FALSE),0)))) splat wrote: Hi I need a different markup formula based on prefix in inventory list ie: ppbolt would get 15% markup (prefix is pp) (Description) detnut would get 20% markup (prefix is det) (Item Code) 3rnail would get 25% markup (prefix is 3) (Item Code) col A would be item code col B would be description (the pp prefix is on some of the items here) col C would be cost col D would be retail Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula based on prefix
Sorry, didn't see your desire for specific columns - you'll need to
change to suit your needs. All that the formula is doing is looking at either the first, first two, or first three letters and comparing it to an exact match (FALSE) in a vlookup table, then returning that value and adding it * the cost of the item to the original cost. S Davis wrote: Create a table on the side, two columns, like so: A B pp 0.15 det 0.20 3 0.25 Now you can write a formula to analyze the first few letters of each inventory. Let's say inventory list starts at D1, and the cost for each inventory is at E1: =E1+E1*(IF(LEFT($D1,3)="det",VLOOKUP($D1,$A$1:$B$3 ,2,FALSE),IF(LEFT($D1,2)="pp",VLOOKUP($D1,$A$1:$B$ 3,2,FALSE),IF(LEFT($D1,1)="3",VLOOKUP($D1,$A$1:$B$ 3,2,FALSE),0)))) splat wrote: Hi I need a different markup formula based on prefix in inventory list ie: ppbolt would get 15% markup (prefix is pp) (Description) detnut would get 20% markup (prefix is det) (Item Code) 3rnail would get 25% markup (prefix is 3) (Item Code) col A would be item code col B would be description (the pp prefix is on some of the items here) col C would be cost col D would be retail Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula based on prefix
I'm not coming right with these formulas, please help.
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula based on prefix
over then add another then down.
-- Regards, Tom Ogilvy "splat" wrote in message s.com... I'm not coming right with these formulas, please help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill data based on an assigned code/prefix | Excel Worksheet Functions | |||
Prefix | Excel Discussion (Misc queries) | |||
SUM without prefix | Excel Discussion (Misc queries) | |||
+= prefix to formula | Excel Discussion (Misc queries) | |||
Adding a prefix to a cell by using a formula | Excel Discussion (Misc queries) |