Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated question...
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated question...
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated question...
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated question...
Sorry, I'm not understanding this!
Biff "ozdemir" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complicated sum formula question | Excel Worksheet Functions | |||
Benefits many people - Question | Excel Discussion (Misc queries) | |||
How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions |