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!

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 Ýãñáøå:
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!

To clarify my question using your notation: Given that C1 = f(x, y), I would
like to create a table with different values of x, y and C1 where f is an
Excel model. If column P has x values, column Q has y values and column R has
C1 values:

P Q R
x1 y1 C11
x2 y2 C12
x3 y3 C13
........

The values in column are dynamically linked with values in P and Q. That is,
for example, if I change value of x2 and y2, value of C12 should also change
as per f.

How can I create a table like this in Excel?

"vezerid" wrote:

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 Î*γÏ�αψε:
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!

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

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.



"Billy Liddel" wrote:

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" wrote:

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.

Ankur

You could copy your summary sheet to the end on the book thus retaining all
the formulas then alter the variables you want and rename your sheet.

If you like you could use macros like the following:
1. Action Copy active sheet to end of workbook
Sub NewSheet()
Dim ns As Integer
Dim rsp
ns = Worksheets.Count
ActiveSheet.Copy after:=Worksheets(ns)
End Sub

2. choose range, operation and amount to generate new model
Sub NewModel()
Dim op As String, rng, c As Variant, amt As Variant
Dim y As String
y = Selection.Address
rng = InputBox("Select the range to change", "Change Range", y, _
100, 100)
Range(rng).Select
op = UCase(InputBox("Enter A, S, M or D to choose Operation", "Operation
Selector"))
amt = InputBox("Enter the changing Amount", "Change figure", "0.00", 100, 100)
If UCase(op) = "A" Then
For Each c In Selection
c.Value = c + amt
Next
ElseIf UCase(op) = "S" Then
For Each c In Selection
c.Value = c - amt
Next
ElseIf UCase(op) = "M" Then
For Each c In Selection
c.Value = c * amt
Next
ElseIf UCase(op) = "D" Then
For Each c In Selection
c.Value = c / amt
Next
End If
End Sub

Is this what you want?
Regards

Peter