Efficient Parametric Studies with Excel

  • Thread starter Thread starter Don Karnage
  • Start date Start date
D

Don Karnage

Hello, I was hoping to find an easy solution to the following problem.
Thanks in advance for any help you might be able to provide.

Say I have a single cell whose value is a function of values of two
other cells

A3 = A2 * A1

If I were to make a multiplication table with A1 on one axis and A2 on
the other, would I be able to fill it out by using the function
specified above? Up until now, I have had to manually enter in each
values into cells A1 and A2 and then 'special paste' the value of A3
into each table entry. This can get quite time consuming for tables
with big dimensions. Is there an easy way to do this? Thanks again.

-Yuto S.
 
Don,

Let's say that you want a table with 8 values for A1, and eight values for
A2, for a total of 64 values.

With your "A1" values in the first row, starting in cell B1 and continuing
to I1, and your "A2" values in column A starting at cell A2 and continuing
down to A9. In cell B2, enter the formula

= $A2*B$1

and copy that cell to B2:I9.

You could also use Excel built-in data table feature: I have pasted the help
item about data tables below.

HTH,
Bernie
MS Excel MVP

Create a two-variable data table
Two-variable data tables use only one formula with two lists of input
values. The formula must refer to two different input cells.
In a cell on the worksheet, enter the formula that refers to the two input
cells.
In the example below, where the formula's starting values are entered in
cells B3, B4, and B5, you would type the formula =PMT(B3/12,B4,-B5) into
cell C2.
Type one list of input values in the same column, below the formula.
In the example below, you would type the different interest rates into cells
C3, C4, and C5.
Type the second list in the same row, to the right of the formula.
In the example below, you would type the loan terms (in months) into cells
D2 and E2.
Select the range of cells that contains the formula and both the row and
column of values.
In the example below, you would select the range C2:E5.
On the Data menu, click Table.
In the Row input cell box, enter the reference to the input cell for the
input values in the row.
In the example below, you would type cell B4 in the Row input cell box.
In the Column input cell box, enter the reference to the input cell for the
input values in the column.
In the example below, you would type B3 in the Column input cell box.
Click OK.
 
Apologies fro my lack of response, and a big thanks to such a thorough
explanation. I didn't even know the 'table' function existed in Excel,
though it seems like a logical feature and considering it's not hidden
away in some obscure array of menus. One problem though. . .what if I
have a complex spreadsheet that say, calculates the drag on an
airplane. The drag may be a function of hundreds of parameters and the
routine may require me to calculate several sub-parameters along the
way. In cases like this, it's almost impossible to be able to cram the
equation into one cell. For example:

A1: altitude
A2: speed
A3: span of aircraft
A4: weight of aircraft
A5: geometry of wing
.....

A6 = a fraction of the total drag as a function of A1 and A2
A7 = a fraction of the total drag as a function of A3 and A4
A8 = a fraction of the total drag as a function of A5

Total drag = A6 + A7 + A8

It seems to be that the Table function wouldn't work in this case
because it requires the user to write out the full expression for drag
in a cell (ie. Total drag as a function of A1, A2, A3, A4, A5). I've
been doing a little experimenting to see if I can coerce Excel into
doing what I want. Thanks again for your help!

-Yuto S.
 
Don -

For example, if you have a list of altitude values in a column, and if you
want corresponding total drag in an adjacent column, enter =A9 at the top of
that adjacent column (where total drag is calculated in cell A9), and then
use the Data Table command, specifying column input A1.

For examples of one-factor and two-factor data tables, in a very different
context, but where the output formula depends on the inputs with many
intermediate variables, see pp. 30-34 in "Decision Trees Using TreePlan,"
file treeplan.pdf, available on the "Download Free Tryout" page at
www.treeplan.com.

- Mike
www.mikemiddleton.com
 
Yuto,

In your case, custom code would probably be better than trying to design a
spreadsheet.

HTH,
Bernie
MS Excel MVP
 
It worked! Thanks to both of you for your help. Bernie, you're
probably right that it's easier to do higher level analysis with Matlab
or something, but an Excel spreadsheet is much easier to distribute to
others and have them understand what's going on.

Take care,
Yuto S.
 
Back
Top