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 A1:D4

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!D1:D4)

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> ozdemir
> ------------------------------------------------------------------------
> ozdemir's Profile:
> http://www.excelforum.com/member.php...o&userid=29426
> View this thread: http://www.excelforum.com/showthread...hreadid=491361
>

[ozdemir]

Biff-

Thanks for the reply. I have not tried it yet but my first thought was 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 sheet 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, when Sheet1!:A1=Sheet2!:$A$1, Sheet1!:B1=Sheet2!:A2, and Sheet1!:C1=Sheet2!:B1. Does this make sense now? I want my sheet 1 to do the calculations and then the result in E6 should be reported to my table in sheet 2.

Thanks for your help!!!

[Biff]

Sorry, I'm not understanding this!

Biff

"ozdemir" <[email protected]> wrote in
message news:[email protected]...
>
> Biff-
>
> Thanks for the reply. I have not tried it yet but my first thought was
> 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 sheet
> 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, when
> Sheet1!:A1=Sheet2!:$A$1, Sheet1!:B1=Sheet2!:A2, and
> Sheet1!:C1=Sheet2!:B1. Does this make sense now? I want my sheet 1 to
> do the calculations and then the result in E6 should be reported to my
> table in sheet 2.
>
> Thanks for your help!!!
>
>
> --
> ozdemir
> ------------------------------------------------------------------------
> ozdemir's Profile:
> http://www.excelforum.com/member.php...o&userid=29426
> View this thread: http://www.excelforum.com/showthread...hreadid=491361
>