pricing help

[james]

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

 

[JBeaucaire]

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?

 

[JBeaucaire]

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"},{10,12,11,9,9.50,14})

 

[JLatham]

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]

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.

 

[james]

worked a treat thanks alot!

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?