Formula How To....

[Guest]

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.

 

[pinmaster]

Hi, I think what you want is an INDEX, MATCH formula:

=INDEX(E2:G4,MATCH(B1,D24,0),MATCH(A1,E1:G1,0))

where
E2:G4 is your range with all the combination
D24 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
J

 

[Guest]

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?