Look-up Table for "packaged pricing"

[Gritz]

Preliminaries:
1) Have looked in archives - either don't know how to describe this well, or
can't find similiar challenge
2) Have looked in my Excel 2003 Help files - either don't know how to
describe well, or can't find similiar challenge
3) Look forward to hearing how y'all would do this...<grin>
=========================================================
CHALLENGE: Build a "look-up" table to calculate/look-up price based on
position of [0s or 1s] or [Blank] in a particular column/position:

- My client's Web-hosting company sends us orders (in excel rows) for items
my client sells - and places a "0" in the column for each of 4 items
- A customer might buy one of 4 items "or" a combination of items
- The excel spreadsheet comes to us with a "0" under the appropriate item
column...for example:
Apples Bannanas Pears Oranges
0 0
- We (presently) manually add a column to extend the pricing
Apples Bannanas Pears Oranges Price
0 0
$12.95
- When we finish filling the order we manually change the 0 to a 1 to show
it has been fulfilled
- Package pricing is "not" a simple A+B = price <frown>
- There are (literally) 15 different prices that are driven from this 4-item
procedure (2**4 - 1)
- Client will be adding four more items and packages (4 now - additional 4
in the future)
- I need a 'look-up table' that allows easy computation of pricing BASED
ENTIRELY on
---whether there is [a 0 or 1] in the column/cell or [it is blank]
---in which column the 0 or 1 exists (first, second, third, or fourth...and
eventually fifth through eighth)

I KNOW THERE ARE MANY PROBLEMS WITH THE LAYOUT AND LOGIC, but I am stuck
with the present form and format

REQUEST:
Can someone help me to determine how I can create a "lookup" table so that
when the spreadsheet has a 0 or 1 in a particular column the 'price' is
placed in the Price column on the same row?

If you wish to communicate directly or ask additional questions I can be
reached at: atradem0 at-sign yahoo (decimal) com

THANK YOU IN ADVANCE for helping us to find our way around a challenging
project

DETAILED LAYOUT FOLLOWS BELOW IF YOU *REALLY* WANT MORE DETAILS:

Apples Bannanas Pears Oranges Price
0 $
4.95
0
$11.95
0 $
9.95
0 $
9.95
0 0
$12.95
0 0
$14.90
0 0
$21.90
0 0 0
$22.90
0 0
$14.90
0 0
$24.90
0 0 0
$22.90
0 0
$1990
0 0 0
$24.85
0 0 0
$24.95
0 0 0 0
$29.90

 

[Mikeopolo]

Hoping I have interpreted your requirements correctly...

With the 4 cells in A2 thru D2, containing any possible combination of
zeros, use this formula in E2:

=1*ISNUMBER(A2)+10*ISNUMBER(B2)+100*ISNUMBER(C2)+1000*ISNUMBER(D2)

This will give you all the possible combinations as numbers (B means
blank):
0BBB = 1
B0BB = 10
BB0B = 100

and so on thru

0000 = 1111

(All 15 values:
1 10 100 1000 11 110 1100 101 1010 1001 111 1110 1011 1010 1111)

Then you can construct a lookup table with 2 columns, the first is all
these values, the second is your price, so if your lookup range of
prices is titled prices, then the above formula could be extended as :

=vlookup(1*ISNUMBER(A2)+10*ISNUMBER(B2)+100*ISNUMBER(C2)+1000*ISNUMBER(D2),prices,2,false).

Hope this is some help. I think when you change the 0's to 1's, the
above formula still works. The principle will work for extended numbers
of columns.

Regards
Mike

 

[Gritz]

Mikeopolo:

THANKS!!!! After playing around a bit I think I even understand how to make
the "new products" work with this idea!!!!!

Again, thanks!!!!

Gritz

 

[Mikeopolo]

Glad it worked for you!

On reflection, I would change the formula to:

=vlookup(1000*ISNUMBER(A2)+100*ISNUMBER(B2)+10*ISNUMBER(C2)+1*ISNUMBER(D2),prices,2,false).

ie reverse the order of the result so that it 'reads' from left to
right, as does your columns. Therefore a value of 1010 means you have a
zero in cols 1 and 3.

Don't know why I reversed them in the first place....hope that doesn't
confuse you.

Mike

 

[Gritz]

Mikeopolo:
Back home we'd say "That's even mo-betta'-yet-still..."

Thanks!!!

Gritz