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.

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

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.