Converting an Excel Model into a Function

[Ankur]

Hello,

I have created an Excel model where I can change the independent variables
to see their effects on dependent variables. I now would like to use this
model as a function. This function will have the independent variables as the
arguments and one of the dependent variables as the result. How can I create
a function that is based on an Excel model?

In a sense it is something like a scenario where Changing Cells are the
independent variables and Result Cells are dependent variables. The scenario
summary would have done the job for me if the results were dynamically linked
to changing cells like in a formula (i.e., changing the value in the Changing
Cells part of the scenario summary immediately updates the values in Result
Cells, as happens in formulas).

Thanks in advance!

 

[vezerid]

Say cell A1 has an input value. Also cell A2 has another input value.
Say B1 = 2*A1+A2
Say C1 = 3*B1+5

C1 is the final cell, effectively holding the function f(x,y) where x
is in A1 and y is in A2.

By substitution:
C1 = 3*B1+5
=3*(2*A1+A2)+5
=6*A1+3*A2+5

Hence C1 contains f(x,y)=6x+3y+5

Does this help?
Kostis Vezerides

Ï/Ç Ankur Ýãñáøå:

 

[Billy Liddel]

Starting from scratch - formulas will produce different results depending on
the variables so if you change one you will get a different answer. You can
create a table by entering the variables putting in a formula in the top row
and copying it down.
Here are 4 tables

Cost Qty Price X Y=X^2 z=(X+Y)^3
23.56 10 235.6 1 1 8
23.56 11 259.16 2 4 216
20.5 10 205 3 9 1728

X Y=X^2 z=X^3 X Y=X^2 z=(X+Y)^3
1 1 1 1 1 8
2 4 8 2 216
3 9 27 3 9 1728

Table 4 is the only interesting one where it will still produce a result if
y = X^2 has been omitted.

Entered in G7 =IF((E7+F7)>0,(E7+F7)^3,(E7+E7^2)^3)

Models are different, call them via Tools, Scenarios you can then give one a
name say Normal for your current X values, then create another say Hi and add
in you new X values.

When you have added all your models you can then switch view Tools,
Scenarios, Select View and click the Show button.

Hope this helps
Peter

 

[Ankur]

Let me start from Scenario Summary (Tools, Scenarios, Summary). Lets say I
have three scenarios: "Good", "Normal" and "Bad" and I generate scenario
summary using Summary option in Scenarios as follows:

Scenario Summary
Current Normal Bad Good
Changing Cells:
Cost 10 10 10 10
Price 12 13 11 15
Demand 4.4 4.4 4 5
Result Cells:
Margin 21% 21% 17% 25%
Profit 96 96 48 96

The Result Cells are linked with Changing Cells in other sheets through
various formulas, lookups and IF conditions - my Excel model.

The problems I have here are these two:

1. If I change Cost under Normal, the Result Cells under Normal (Margin and
Profit) do not change (because they are not linked through a formula anymore
on this summary table).

2. It is tedious to create many scenarios using Tools, Scenarios, Add
button. I already have a table for many more cases (beyond Normal, Good, Bad)
for which I need to generate results using the Excel model I already have.

Both of these problems can be solved if there is a way I can convert my
Excel model into a function or a formula. The Excel model I have is far too
complicated to be reduced to a single formula expression.