Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula How To....
I have what seems like a simple problem to solve, but I can not seem to
figure it out. Imagine the following: Cost of Widget is based on three variables: A, B, C. So, if I enter a value for A, B and C, then the cost is calculated based on these inputs (several steps of calculations and iterations, etc.). I want to put a spreadsheet together that lists a range of values of A and B with a constant value of C, where each cell would contain the cost those inputs. i.e. Cell B1=10 Cell B2=20 Cell B3=30 Cell B5=20*B1 Cell B6=B5*B2 Cell B7=B3*B6+B5 Cell B9=sum(B5:B7) COST OF WIDGET Thus, B9 is a function of B1, B2 and B3. So, if i have a table where the column headings are a range of values for B1 and the row headings are a range of values for B2, and I fix B3 for each table, then each cell within the table would report a cost depending what value of B1 and B2 are used. I hope this makes sense to someone out there. I need help and I don't know how to do this. I need to provide a supplier or ours a price sheet. I don't want to reveal how i calculate the costs, i just want to give him a spreadsheet with the costs and nothing more. This is the reason for my question. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula How To....
Hi, I think what you want is an INDEX, MATCH formula: =INDEX(E2:G4,MATCH(B1,D2:D4,0),MATCH(A1,E1:G1,0)) where E2:G4 is your range with all the combination D2:D4 is your row header E1:G1 is your column header A1 is a value from your column header B1 is a value from row header HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495377 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula How To....
Assuming A2:A6 contain values for your variable B1, cell B1 through F1
contain values for your variable B2, and cell A1 contains your constant for variable B3, you could enter this formula in cell B2, then copy it across and down to fill your table. =20*$A2*((B$1*(1+$A$1))+2) Then print out the table. Is this close to what you are looking for? "Cihan Ozdemir" wrote: I have what seems like a simple problem to solve, but I can not seem to figure it out. Imagine the following: Cost of Widget is based on three variables: A, B, C. So, if I enter a value for A, B and C, then the cost is calculated based on these inputs (several steps of calculations and iterations, etc.). I want to put a spreadsheet together that lists a range of values of A and B with a constant value of C, where each cell would contain the cost those inputs. i.e. Cell B1=10 Cell B2=20 Cell B3=30 Cell B5=20*B1 Cell B6=B5*B2 Cell B7=B3*B6+B5 Cell B9=sum(B5:B7) COST OF WIDGET Thus, B9 is a function of B1, B2 and B3. So, if i have a table where the column headings are a range of values for B1 and the row headings are a range of values for B2, and I fix B3 for each table, then each cell within the table would report a cost depending what value of B1 and B2 are used. I hope this makes sense to someone out there. I need help and I don't know how to do this. I need to provide a supplier or ours a price sheet. I don't want to reveal how i calculate the costs, i just want to give him a spreadsheet with the costs and nothing more. This is the reason for my question. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Hide formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |