Efficient Parametric Studies with Excel

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.


"Don Karnage" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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

"Don Karnage" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

Yuto,

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

HTH,
Bernie
MS Excel MVP


"Don Karnage" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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.