Complicated question...

[ozdemir]

I have a spreadsheet that accepts three inputs, height, depth and width.
With these inputs our costs are calculated and the end result i am
interested in is the price. This spreadsheet only works though for
individual inputs of each variable, meaning i have to manually enter
each value to get a price.

I want to create a table that will have multiple variables of height,
depth and width and calculate and report the price in this table. How
do I do this? I am not an expert at utlizing excel's ability to
program, so that's why I ask the question here.

Do i need to explain more? Let me know since its late when I am
writing this and don't know if i missed something.

Thanks for your help.

ozdemir

 

[Biff]

Hi!

You need to create a 4 column table that lists all the variables:

Assume the table is on Sheet2 in the range A14

Height.....Depth.....Width.....Price
.....1............1.............1..........10
.....1.5.........1.............1.........10.50
.....1.5.........1.5..........1.5.......12.50
......2............1............1..........15

Then you can have 3 cells for the input on Sheet1:

A1 = Height = 1.5
A2 = Depth = 1
A3 = Width = 1

The formula for the price:

=SUMPRODUCT(--(Sheet2!A1:A4=A1),--(Sheet2!B1:B4=A2),--(Sheet2!C1:C4=A3),Sheet2!D14)

You can even get "fancy" and give each one of the variable columns of the
table a descriptive defined name then the formula could look like this:

=SUMPRODUCT(--(Height=A1),--(Depth=A2),--(Width=A3),Price)

Biff

 

[ozdemir]

Biff-

Thanks for the reply. I have not tried it yet but my first thought wa
it won't work.

Sheet 1 has the following:

A1=Height
B1=Depth
C1=Width

E6=Price (which is the summation of various different cells in shee
1)

I want sheet 2 to have those inputs listed as follows:

A1=Height (Constant)

B1:B10 (Range of Width values from 20-40)
A2:A10 (Range of Depth values from 20-40)

Then cell B2 would report the value of Sheet1!:E6, whe
Sheet1!:A1=Sheet2!:$A$1, Sheet1!:B1=Sheet2!:A2, an
Sheet1!:C1=Sheet2!:B1. Does this make sense now? I want my sheet 1 t
do the calculations and then the result in E6 should be reported to m
table in sheet 2.

Thanks for your help!!

 

[Biff]

Sorry, I'm not understanding this!

Biff