Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
pricing help
hi everyone, i'm a cabinet maker trying to make life a bit easier for myself.
i'm trying to make a sheetto price wood by area. so i have a cell (a5) with a dropdown list to select wood type b5 is length c5 is height d5 is area (b5 times c5) e5 i need to be unit price governed by wood type. eg; if a5=ash then e5=20(wood price) f5 will be d5 times e5, its the unit price part i'm stuck on. can anyone help? thanks James |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
pricing help
1) LOOKUP TABLE
Create an alphabetical table in two columns. You may already have a list somewhere being used for the data validation list in column A. Let's say that list in cells M1:M10...then in N1:N10 put the matching unit price that goes with each wood type in M1:M10 Next, use a formula like this in E5 to use that lookup table: =LOOKUP(A5, $M$1:$M$10, $N$1:$N$10) Remember that list of word types must be alphabetical for that to work right. If you want to use the same list but don't want the list to have to be alphabetical, then this would work in E5 instead: =VLOOKUP(A5, $M$1:$N10,2,FALSE) ....or =INDEX($N$1:$N$10, MATCH(A5, $M$1:$M$10, FALSE) If you want E5 to stay empty until A5 has a value, use these versions: =IF(A5="", "", LOOKUP(A5, $M$1:$M$10, $N$1:$N$10)) =IF(A5="", "", VLOOKUP(A5, $M$1:$N10,2,FALSE)) =IF(A5="", "", INDEX($N$1:$N$10, MATCH(A5, $M$1:$M$10, FALSE)) Does that help? -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "james" wrote: hi everyone, i'm a cabinet maker trying to make life a bit easier for myself. i'm trying to make a sheetto price wood by area. so i have a cell (a5) with a dropdown list to select wood type b5 is length c5 is height d5 is area (b5 times c5) e5 i need to be unit price governed by wood type. eg; if a5=ash then e5=20(wood price) f5 will be d5 times e5, its the unit price part i'm stuck on. can anyone help? thanks James |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
pricing help
2) LOOKUP w/INLINE ARRAYs
Of you don't want to maintain a separate table on your sheet, you can build the table into a Lookup formula. Again, remember the first array of wood types must be alphabetical for this to work properly. =LOOKUP(A5, {"Ash","Birch","Cedar","Maple","Oak","Redwood"},{1 0,12,11,9,9.50,14}) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "james" wrote: hi everyone, i'm a cabinet maker trying to make life a bit easier for myself. i'm trying to make a sheetto price wood by area. so i have a cell (a5) with a dropdown list to select wood type b5 is length c5 is height d5 is area (b5 times c5) e5 i need to be unit price governed by wood type. eg; if a5=ash then e5=20(wood price) f5 will be d5 times e5, its the unit price part i'm stuck on. can anyone help? thanks James |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
pricing help
James,
JBeaucaire has provided excellent ways to do this. Any of them should work for you quite well. I just wanted to add "think ahead a little"... if you think that your list of woods will change in the future, OR that the price of any individual type is likely to change, then the lookup table is much easier to maintain: you only have to make changes in one place. You might also look into using named ranges to give your table a name to refer to it in formulas. That way if you make additions to the list of woods/prices, and you insert new rows within the existing table area, your formulas automatically 'fix' themselves to pick up on any added/deleted entries in it. "JBeaucaire" wrote: 2) LOOKUP w/INLINE ARRAYs Of you don't want to maintain a separate table on your sheet, you can build the table into a Lookup formula. Again, remember the first array of wood types must be alphabetical for this to work properly. =LOOKUP(A5, {"Ash","Birch","Cedar","Maple","Oak","Redwood"},{1 0,12,11,9,9.50,14}) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "james" wrote: hi everyone, i'm a cabinet maker trying to make life a bit easier for myself. i'm trying to make a sheetto price wood by area. so i have a cell (a5) with a dropdown list to select wood type b5 is length c5 is height d5 is area (b5 times c5) e5 i need to be unit price governed by wood type. eg; if a5=ash then e5=20(wood price) f5 will be d5 times e5, its the unit price part i'm stuck on. can anyone help? thanks James |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
pricing help
I used to make those same admonitions, JL. But truth is most tables like
this get used in one place, so it's really not much difference to set aside a chart and have to edit it vs change the formula in the one LOOKUP w/INLINE ARRAY and double-click to copy the new formula down the whole column. It's much the same. Once i realized this was true for many of my lookup scenarios, I switched to using inline arrays for most of my "small" arrays, up to about 5-6 values seems manageable. Above that, I stick with the table. -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "JLatham" wrote: James, JBeaucaire has provided excellent ways to do this. Any of them should work for you quite well. I just wanted to add "think ahead a little"... if you think that your list of woods will change in the future, OR that the price of any individual type is likely to change, then the lookup table is much easier to maintain: you only have to make changes in one place. You might also look into using named ranges to give your table a name to refer to it in formulas. That way if you make additions to the list of woods/prices, and you insert new rows within the existing table area, your formulas automatically 'fix' themselves to pick up on any added/deleted entries in it. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
pricing help
worked a treat thanks alot!
"JBeaucaire" wrote: 1) LOOKUP TABLE Create an alphabetical table in two columns. You may already have a list somewhere being used for the data validation list in column A. Let's say that list in cells M1:M10...then in N1:N10 put the matching unit price that goes with each wood type in M1:M10 Next, use a formula like this in E5 to use that lookup table: =LOOKUP(A5, $M$1:$M$10, $N$1:$N$10) Remember that list of word types must be alphabetical for that to work right. If you want to use the same list but don't want the list to have to be alphabetical, then this would work in E5 instead: =VLOOKUP(A5, $M$1:$N10,2,FALSE) ...or =INDEX($N$1:$N$10, MATCH(A5, $M$1:$M$10, FALSE) If you want E5 to stay empty until A5 has a value, use these versions: =IF(A5="", "", LOOKUP(A5, $M$1:$M$10, $N$1:$N$10)) =IF(A5="", "", VLOOKUP(A5, $M$1:$N10,2,FALSE)) =IF(A5="", "", INDEX($N$1:$N$10, MATCH(A5, $M$1:$M$10, FALSE)) Does that help? -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "james" wrote: hi everyone, i'm a cabinet maker trying to make life a bit easier for myself. i'm trying to make a sheetto price wood by area. so i have a cell (a5) with a dropdown list to select wood type b5 is length c5 is height d5 is area (b5 times c5) e5 i need to be unit price governed by wood type. eg; if a5=ash then e5=20(wood price) f5 will be d5 times e5, its the unit price part i'm stuck on. can anyone help? thanks James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Statement for Pricing | Excel Discussion (Misc queries) | |||
Pricing Tiers Help | Excel Discussion (Misc queries) | |||
pricing | Excel Discussion (Misc queries) | |||
Retail pricing to the $x.x9 or $x.x5 | Excel Discussion (Misc queries) | |||
Old to New Pricing | Excel Worksheet Functions |