Nested IF functions

[Pickle]

I have a spread sheet which I am trying to build for costing. Users of the
costing sheet will need to input the following

length =
height =
type =
width =

Lengths can be 2, 2.5, 3 or 4, height can be 6, 7, 8 or 9, type can be a, b
,c, d, and width can be 1, 2, 3 or 4. Each compbination of these give a
unique cost value.
I tried to do this using a nested IF statement, but this restricts me to 7
nestings, leaving me well short of the numbers of combinations required.

If there another way that I can do this I'd appreciate advice on it.

 

[William]

Hi Pickle

Create a table with 5 columns showing every permutation possible for length,
height, type and width. In the fifth column, enter the corresponding cost
for the combination in the preceding four columns.

Assign names to the relevant ranges in each column - for example, cells
C2:C50 name "height", D220 name "length", E2:E50 name "width", F2:F50 name
"type" and G2:G50 name "amount".

In cells B1:B4 enter the relevant combination you want to find for height,
length, type and width.

This formula will then return the corresponding costs for the values entered
in B1:B4...
=SUMPRODUCT((height=B1)*(length=B2)*(type=B3)*(width=B4)*Amount)

--
XL2002
Regards

William

(e-mail address removed)

| I have a spread sheet which I am trying to build for costing. Users of the
| costing sheet will need to input the following
|
| length =
| height =
| type =
| width =
|
| Lengths can be 2, 2.5, 3 or 4, height can be 6, 7, 8 or 9, type can be a,
b
| ,c, d, and width can be 1, 2, 3 or 4. Each compbination of these give a
| unique cost value.
| I tried to do this using a nested IF statement, but this restricts me to 7
| nestings, leaving me well short of the numbers of combinations required.
|
| If there another way that I can do this I'd appreciate advice on it.

 

[unlikeKansas]

One way which works, although it's a bit messy is to create a list of all the
possible permutations of your 4 input values and their corresponding unique
cost value.

In your case there would be 256 possible combinations, the following are
some examples:


26a1
26a2
26a3
26a4
26b1
26b2
26b3
26b4
26c1
26c2
etc. to 256

You can then use the following formula to lookup the value you want:

=VLOOKUP(CONCATENATE(A1,B1,C1,D1),E1:F256,2,0)

Where A1, B1, C1 & D1 contain your inputs and E1:F256 contains your 256
permutations and their unique cost value.

unlikeKansas

 

[Biff]

Hi Pickle!

The solution depends on how you have your pricing table
setup. Need to see a sample.

Biff